TIME_TO_SEC(): strips off the date part and returns the time part as the corresponding number of seconds:

      
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.01 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 TIME_TO_SEC(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

1.	Breaking Down Time Intervals into Components
2.	If you pass TIME_TO_SEC( ) a date-and-time value, it extracts the time part and discards the date.
3.	TIME_TO_SEC( ) converts a TIME value to the equivalent number of seconds
4.	To compute the total elapsed time, use TIME_TO_SEC( ) to convert the values to seconds before summing them.
5.	Using TIME_TO_SEC( ) to strip off the date part of the t_create values
6.	Transform the time differences into seconds using TIME_TO_SEC, add them, and then convert them back
7.	To express time values as minutes, hours, or days, perform the appropriate divisions:
8.	Forcing MySQL to Treat Strings as Temporal Values

TIME_TO_SEC(): strips off the date part and returns the time part as the corresponding number of seconds: : TIME_TO_SEC « Date Time « SQL / MySQL

Related examples in the same category