Database Reference
In-Depth Information
| 1970-01-01 00:00:00 | 719528 | 1970-01-01 |
| 1999-12-31 09:00:00 | 730484 | 1999-12-31 |
| 2000-06-04 15:45:30 | 730640 | 2000-06-04 |
| 2017-03-16 12:30:15 | 736769 | 2017-03-16 |
+---------------------+-------------------+-------------------+
Converting between date-and-time values and seconds
For
DATETIME
or
TIMESTAMP
values that lie within the range of the
TIMESTAMP
data type
(from the beginning of 1970 partially through 2038), the
UNIX_TIMESTAMP()
and
FROM_UNIXTIME()
functions convert 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 (
TIME_TO_SEC()
is unsuitable for this because
it discards the date):
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 |
| 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 |
| 2017-03-16 12:30:15 | 1489685415 | 2017-03-16 12:30:15 |
+---------------------+------------+---------------------+
There is a relationship between “UNIX” in the function names and the fact that the
applicable range of values begins with 1970: the “Unix epoch” begins at
1970-01-01
00:00:00
UTC. 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. Why
isn't it
0
? The apparent discrepancy is due to the fact that the MySQL server interprets
the
UNIX_TIMESTAMP()
argument as a value in the client's local time zone and converts
it to UTC (see
Recipe 6.4
). My server is in the US Central time zone, six hours (21,600
seconds) west of UTC. Change the session time zone to
'+00:00'
for UTC time and
run the query again to observe a different result:
mysql>
set time_zone = '+00:00';
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 | 0 | 1970-01-01 00:00:00 |
| 1999-12-31 09:00:00 | 946630800 | 1999-12-31 09:00:00 |
