Sorting by Time of Day
Problem
You want to sort rows in time-of-day order.
Solution
Pull out the hour, minute, and second from the column that contains the time, and use them for sorting.
Discussion
Time-of-day sorting can be done different ways, depending on
your column type. If the values are stored in a TIME
column named timecol
, just sort them directly using
ORDER
BY
timecol
. To put
DATETIME
or TIMESTAMP
values in time-of-day order,
extract the time parts and sort them. For example, the mail
table contains DATETIME
values, which can be sorted by time
of day like this:
mysql>SELECT * FROM mail ORDER BY HOUR(t), MINUTE(t), SECOND(t);
+---------------------+---------+---------+---------+---------+---------+
| t | srcuser | srchost | dstuser | dsthost | size |
+---------------------+---------+---------+---------+---------+---------+
| 2006-05-15 07:17:48 | gene | mars | gene | saturn | 3824 |
| 2006-05-15 08:50:57 | phil | venus | phil | venus | 978 |
| 2006-05-16 09:00:28 | gene | venus | barb | mars | 613 |
| 2006-05-14 09:31:37 | gene | venus | barb | mars | 2291 |
| 2006-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2006-05-15 10:25:52 | gene | mars | tricia | saturn | 998532 |
| 2006-05-14 11:52:17 | phil | mars | tricia | saturn | 5781 |
| 2006-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
...
You can also use
TIME_TO_SEC()
,
which strips off the date part and returns the time part as the
corresponding number of seconds:
mysql>SELECT * FROM mail ORDER BY ...
Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.