MySQL Cookbook

Sorting by Time of Day

Problem

You want to sort 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, just sort them directly. 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    |
+---------------------+---------+---------+---------+---------+---------+
| 2001-05-15 07:17:48 | gene    | mars    | gene    | saturn  |    3824 |
| 2001-05-15 08:50:57 | phil    | venus   | phil    | venus   |     978 |
| 2001-05-16 09:00:28 | gene    | venus   | barb    | mars    |     613 |
| 2001-05-14 09:31:37 | gene    | venus   | barb    | mars    |    2291 |
| 2001-05-11 10:15:08 | barb    | saturn  | tricia  | mars    |   58274 |
| 2001-05-15 10:25:52 | gene    | mars    | tricia  | saturn  |  998532 |
| 2001-05-14 11:52:17 | phil    | mars    | tricia  | saturn  |    5781 |
| 2001-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 TIME_TO_SEC(t); +---------------------+---------+---------+---------+---------+---------+ ...