MySQL datetime field query sort by hour:minute
12,096
ORDER BY HOUR(date_time), MINUTE(date_time), date_time
or
ORDER BY TIME(date_time), date_time
Comments
-
Peter almost 2 years
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...
-
mickmackusa about 4 yearsCode-only answers are low value on Stackoverflow because they do very little to educate/empower thousands of future researchers.
-
mickmackusa about 4 yearsCode-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.