Sorting by Time of Day
mysql>
mysql> CREATE TABLE mail
-> (
-> t DATETIME, # when message was sent
-> senderUser CHAR(8), # sender (source user and host)
-> senderHost CHAR(20),
-> recipientUser CHAR(8), # recipient (destination user and host)
-> recipientHost CHAR(20),
-> size BIGINT, # message size in bytes
-> INDEX (t)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO mail (t,senderHost,senderUser,recipientHost,recipientUser,size)
-> VALUES
-> ('2010-05-11 10:15:08','saturn','barb','mars','tricia',58274),
-> ('2010-05-12 12:48:13','mars','tricia','venus','gene',194925),
-> ('2010-05-12 15:02:49','mars','phil','saturn','phil',1048),
-> ('2010-05-13 13:59:18','saturn','barb','venus','tricia',271),
-> ('2010-05-14 09:31:37','venus','gene','mars','barb',2291),
-> ('2010-05-14 11:52:17','mars','phil','saturn','tricia',5781),
-> ('2010-05-14 14:42:21','venus','barb','venus','barb',98151),
-> ('2010-05-14 17:03:01','saturn','tricia','venus','phil',2394482),
-> ('2010-05-15 07:17:48','mars','gene','saturn','gene',3824),
-> ('2010-05-15 08:50:57','venus','phil','venus','phil',978),
-> ('2010-05-15 10:25:52','mars','gene','saturn','tricia',998532),
-> ('2010-05-15 17:35:31','saturn','gene','mars','gene',3856),
-> ('2010-05-16 09:00:28','venus','gene','mars','barb',613),
-> ('2010-05-16 23:04:19','venus','phil','venus','barb',10294),
-> ('2010-05-17 12:49:23','mars','phil','saturn','tricia',873),
-> ('2010-05-19 22:21:51','saturn','gene','venus','gene',23992);
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT * FROM mail ORDER BY HOUR(t), MINUTE(t), SECOND(t);
+---------------------+------------+------------+---------------+---------------+---------+
| t | senderUser | senderHost | recipientUser | recipientHost | size |
+---------------------+------------+------------+---------------+---------------+---------+
| 2010-05-15 07:17:48 | gene | mars | gene | saturn | 3824 |
| 2010-05-15 08:50:57 | phil | venus | phil | venus | 978 |
| 2010-05-16 09:00:28 | gene | venus | barb | mars | 613 |
| 2010-05-14 09:31:37 | gene | venus | barb | mars | 2291 |
| 2010-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2010-05-15 10:25:52 | gene | mars | tricia | saturn | 998532 |
| 2010-05-14 11:52:17 | phil | mars | tricia | saturn | 5781 |
| 2010-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
| 2010-05-17 12:49:23 | phil | mars | tricia | saturn | 873 |
| 2010-05-13 13:59:18 | barb | saturn | tricia | venus | 271 |
| 2010-05-14 14:42:21 | barb | venus | barb | venus | 98151 |
| 2010-05-12 15:02:49 | phil | mars | phil | saturn | 1048 |
| 2010-05-14 17:03:01 | tricia | saturn | phil | venus | 2394482 |
| 2010-05-15 17:35:31 | gene | saturn | gene | mars | 3856 |
| 2010-05-19 22:21:51 | gene | saturn | gene | venus | 23992 |
| 2010-05-16 23:04:19 | phil | venus | barb | venus | 10294 |
+---------------------+------------+------------+---------------+---------------+---------+
16 rows in set (0.00 sec)
mysql>
mysql> drop table mail;
Query OK, 0 rows affected (0.00 sec)
Related examples in the same category