MySQL datetime field query sort by hour:minute

Question

I have a table like this:

id  date_time
1   2/11/2013 7:05
2   2/11/2013 7:00
3   2/12/2013 7:00
4   2/14/2013 7:00
5   2/16/2013 7:00
6   2/17/2013 7:00
7   2/12/2013 7:05
8   2/14/2013 7:05
9   2/15/2013 7:05
10  2/16/2013 7:05
11  2/17/2013 7:05
12  2/13/2013 7:00
13  2/15/2013 7:00
14  2/13/2013 7:05

I need it sorted by HOUR:MINUTE and than sorted by DATE so I get something like this on output:

2/11/2013 7:00
2/12/2013 7:00
2/13/2013 7:00
2/14/2013 7:00
2/15/2013 7:00
2/16/2013 7:00
2/17/2013 7:00
2/11/2013 7:05
2/12/2013 7:05
2/13/2013 7:05
2/14/2013 7:05
2/15/2013 7:05
2/16/2013 7:05
2/17/2013 7:05

Is there a way to sort output directly with MySQL?... I know to do it via PHP once I get the query results, but just wonder if MySQL can do something like that ?

I tried a query like this:

SELECT * FROM my_table WHERE (date_time BETWEEN '$date_check_low' AND '$date_check_high') ORDER BY hour(date_time) ASC

but it produces weird results...

score 5 · Accepted Answer · answered Feb 10 '13 at 11:42

5

ORDER BY HOUR(date_time), MINUTE(date_time), date_time

or

ORDER BY TIME(date_time), date_time

SQLFiddle Demo

answered Feb 10 '13 at 11:42

John Woo

238,432
61
456
464

Code-only answers are low value on Stackoverflow because they do very little to educate/empower thousands of future researchers. – mickmackusa Feb 29 '20 at 02:41

score 0 · Answer 2 · answered Feb 10 '13 at 11:42

0

Try this:

SELECT *
FROM my_table
WHERE (date_time BETWEEN '$date_check_low' AND '$date_check_high')
ORDER BY time(date_time),
         date_time;

answered Feb 10 '13 at 11:42

Hamlet Hakobyan

31,621
6
49
65

Code-only answers are low value on Stackoverflow because they do very little to educate/empower thousands of future researchers. Injecting variables directly into the query may lead to vulnerabilities/instability. – mickmackusa Feb 29 '20 at 02:42

MySQL datetime field query sort by hour:minute

2 Answers2