5.13 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.
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:
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. 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.
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 |
+------------+------------------------------------------+
|
