未加星标

MySQL result ORDER BY date and time

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二03 | 时间 2018 | 作者 红领巾 ] 0人收藏点击收藏

I have query like this

"SELECT booking.referance_no AS ref_no, booking.entry_date AS dep_date, TIME_FORMAT(booking.entry_time,'%H:%i') as dep_time, booking.first_name AS fname, booking.last_name AS lname, booking.mobile AS mobile FROM booking WHERE $WHERE1 GROUP BY booking.book_id UNION ALL SELECT supplier.reference_no AS ref_no, DATE_FORMAT(supplier.departure_date_time,'%d-%m-%Y') AS dep_date, TIME_FORMAT(supplier.departure_date_time,'%H:%i') AS dep_time, supplier.fname AS fname, supplier.lname AS lname, supplier_subscribers.mobile AS mobile FROM supplier WHERE $WHERE2 ORDER BY `dep_date` ASC, `dep_time` ASC";

and Where Clause

$WHERE1="STR_TO_DATE(booking.entry_date, '%d-%M-%Y') BETWEEN '".$from_date." 00:00:00' AND '".$to_date." 23:59:59'"; $WHERE2="supplier.departure_date_time BETWEEN '".$from_date." 00:00:00' AND '".$to_date." 23:59:59'"; Scenario One

When searching within 24 hours and ORDER BY dep_date ASC, dep_time ASC I'm getting result like this


MySQL result ORDER BY date and time

check line 63 and 64 in above snapshot the result is sorted by date but not by time also first result showing from supplier table till line 63 and then from 64 onward from table booking , it's like the result is sorted by each table (first supplier table and then booking table) and then sorted by date and time .

And if search within 24 hours with only ORDER BY dep_time ASC I get the correct result properly sorted by time.

Scenario Two

If search within 48 hours or more with only ORDER BY dep_time ASC I get result as follow


MySQL result ORDER BY date and time

Not Sorted by Date off course it was only sorted by time , so it's useless.

And if search within 48 hours or more with ORDER BY dep_date ASC, dep_time ASC I get the same result as first snapshot like;

First result sorted by table supplier and then booking but first 24 hours and then next 24 hours Then within each 24 hours each table result sorted by date and time separately.

So how can I sort the result of both tables against date and time no matter what will be the date range?

Use Concat

ORDER BY concat(dep_date,' ',dep_time)` ASC

Try the below query, hope this works

select * from (SELECT booking.referance_no AS ref_no, booking.entry_date AS dep_date, TIME_FORMAT(booking.entry_time,'%H:%i') as dep_time, booking.first_name AS fname, booking.last_name AS lname, booking.mobile AS mobile FROM booking WHERE $WHERE1 GROUP BY booking.book_id UNION ALL SELECT supplier.reference_no AS ref_no, DATE_FORMAT(supplier.departure_date_time,'%d-%m-%Y') AS dep_date, TIME_FORMAT(supplier.departure_date_time,'%H:%i') AS dep_time, supplier.fname AS fname, supplier.lname AS lname, supplier_subscribers.mobile AS mobile FROM supplier WHERE $WHERE2 ) as temp ORDER BY concat(dep_date,' ',dep_time)` ASC

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

分页:12
转载请注明
本文标题:MySQL result ORDER BY date and time
本站链接:https://www.codesec.net/view/611868.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(12)