Converting Between Date-and-Time Values and Seconds

5.13.1 Problem

You have a date-and-time value but want a value in seconds, or vice versa.

5.13.2 Solution

The UNIX_TIMESTAMP( ) and FROM_UNIXTIME( ) functions convert DATETIME or TIMESTAMP values in the range from 1970 through approximately 2037 to and from the number of seconds elapsed since the beginning of 1970. The conversion to seconds offers higher precision for date-and-time values than a conversion to days, at the cost of a more limited range of values for which the conversion may be performed.

5.13.3 Discussion

When working with date-and-time values, you can use TO_DAYS( ) and FROM_DAYS( ) to convert date values to days and back to dates, as shown in the previous section. For values that occur no earlier than 1970-01-01 00:00:00 GMT and no later than approximately 2037, it's possible to achieve higher precision by converting to and from seconds.[2] UNIX_TIMESTAMP( ) converts date-and-time values in this range to the number of seconds elapsed since the beginning of 1970, and FROM_UNIXTIME( ) does the opposite:

[2] It's difficult to give a precise upper bound on the range of values because it varies somewhat between systems.

mysql> SELECT dt,
 -> UNIX_TIMESTAMP(dt) AS seconds,
 -> FROM_UNIXTIME(UNIX_TIMESTAMP(dt)) AS timestamp
 -> FROM datetime_val;
+---------------------+-----------+---------------------+
| dt | seconds | timestamp |
+---------------------+-----------+---------------------+
| 1970-01-01 00:00:00 | 21600 | 1970-01-01 00:00:00 |
| 1987-03-05 12:30:15 | 541967415 | 1987-03-05 12:30:15 |
| 1999-12-31 09:00:00 | 946652400 | 1999-12-31 09:00:00 |
| 2000-06-04 15:45:30 | 960151530 | 2000-06-04 15:45:30 |
+---------------------+-----------+---------------------+

The relationship between the "UNIX" in the function names and the fact that the applicable range of values begins with 1970 is that 1970-01-01 00:00:00 GMT marks the "Unix epoch." The epoch is time zero, or the reference point for measuring time in Unix systems.[3] That being so, you may find it curious that the preceding example shows a UNIX_TIMESTAMP( ) value of 21600 for the first value in the datetime_val table. What's going on? Why isn't it 0? The apparent discrepancy is due to the fact that the MySQL server converts values to its own time zone when displaying them. My server is in the U.S. Central Time zone, which is six hours (that is, 21600 seconds) west of GMT.

[3] 1970-01-01 00:00:00 GMT also happens to be the epoch as Java measures time.

UNIX_TIMESTAMP( ) can convert DATE values to seconds, too. It treats such values as having an implicit time-of-day part of 00:00:00:

mysql> SELECT CURDATE( ), FROM_UNIXTIME(UNIX_TIMESTAMP(CURDATE( )));
+------------+------------------------------------------+
| CURDATE( ) | FROM_UNIXTIME(UNIX_TIMESTAMP(CURDATE( ))) |
+------------+------------------------------------------+
| 2002-07-15 | 2002-07-15 00:00:00 |
+------------+------------------------------------------+