Pull out the hour, minute, and second from the column that contains the time, and use them for sorting.
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 TIME_TO_SEC(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 |
...
3.136.18.141