Converting Between Date-and-Time Values and Seconds
Problem
You have a date-and-time value but want a value in seconds, or vice versa.
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.
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.[29]
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 | | ...