You have a function temporal value such as a time or date that you want to convert to basic units such as seconds or days. This is often useful or necessary for performing temporal arithmetic operations (Recipes and ).
The conversion method depends on the type of value to be converted:
The following discussion shows how to convert several types of temporal values to basic units and vice versa.
TIME
values are specialized
representations of a simpler unit (seconds), so you can convert back
and forth from one to the other using the TIME_TO_SEC()
and SEC_TO_TIME()
functions.
TIME_TO_SEC()
converts a TIME
value to the
equivalent number of seconds, and SEC_TO_TIME()
does the opposite. The
following statement demonstrates a simple conversion in both
directions:
mysql>SELECT t1,
->TIME_TO_SEC(t1) AS 'TIME to seconds',
->SEC_TO_TIME(TIME_TO_SEC(t1)) AS 'TIME to seconds to TIME'
->FROM time_val;
+----------+-----------------+-------------------------+ | t1 | TIME to seconds | TIME to seconds to TIME | +----------+-----------------+-------------------------+ | 15:00:00 | 54000 | 15:00:00 | | 05:01:30 | 18090 | 05:01:30 | | 12:30:20 | 45020 | 12:30:20 | +----------+-----------------+-------------------------+
To express time values as minutes, hours, or days, perform the appropriate divisions:
mysql>SELECT t1,
->TIME_TO_SEC(t1) AS 'seconds',
->TIME_TO_SEC(t1)/60 AS 'minutes',
->TIME_TO_SEC(t1)/(60*60) AS 'hours',
->TIME_TO_SEC(t1)/(24*60*60) AS 'days'
->FROM time_val;
+----------+---------+----------+---------+--------+ | t1 | seconds | minutes | hours | days | +----------+---------+----------+---------+--------+ | 15:00:00 | 54000 | 900.0000 | 15.0000 | 0.6250 | | 05:01:30 | 18090 | 301.5000 | 5.0250 | 0.2094 | | 12:30:20 | 45020 | 750.3333 | 12.5056 | 0.5211 | +----------+---------+----------+---------+--------+
Use FLOOR()
if you
prefer integer values that have no fractional part:
mysql>SELECT t1,
->TIME_TO_SEC(t1) AS 'seconds',
->FLOOR(TIME_TO_SEC(t1)/60) AS 'minutes',
->FLOOR(TIME_TO_SEC(t1)/(60*60)) AS 'hours',
->FLOOR(TIME_TO_SEC(t1)/(24*60*60)) AS 'days'
->FROM time_val;
+----------+---------+---------+-------+------+ | t1 | seconds | minutes | hours | days | +----------+---------+---------+-------+------+ | 15:00:00 | 54000 | 900 | 15 | 0 | | 05:01:30 | 18090 | 301 | 5 | 0 | | 12:30:20 | 45020 | 750 | 12 | 0 | +----------+---------+---------+-------+------+
If you pass TIME_TO_SEC()
a date-and-time value,
it extracts the time part and discards the date. This provides yet
another means of extracting times from DATETIME
and TIMESTAMP
values (in addition to
those already discussed in Extracting Parts of Dates or Times):
mysql>SELECT dt,
->TIME_TO_SEC(dt) AS 'time part in seconds',
->SEC_TO_TIME(TIME_TO_SEC(dt)) AS 'time part as TIME'
->FROM datetime_val;
+---------------------+----------------------+-------------------+ | dt | time part in seconds | time part as TIME | +---------------------+----------------------+-------------------+ | 1970-01-01 00:00:00 | 0 | 00:00:00 | | 1987-03-05 12:30:15 | 45015 | 12:30:15 | | 1999-12-31 09:00:00 | 32400 | 09:00:00 | | 2000-06-04 15:45:30 | 56730 | 15:45:30 | +---------------------+----------------------+-------------------+ mysql>SELECT ts,
->TIME_TO_SEC(ts) AS 'time part in seconds',
->SEC_TO_TIME(TIME_TO_SEC(ts)) AS 'time part as TIME'
->FROM timestamp_val;
+---------------------+----------------------+-------------------+ | ts | time part in seconds | time part as TIME | +---------------------+----------------------+-------------------+ | 1970-01-01 00:00:00 | 0 | 00:00:00 | | 1987-03-05 12:30:15 | 45015 | 12:30:15 | | 1999-12-31 09:00:00 | 32400 | 09:00:00 | | 2000-06-04 15:45:30 | 56730 | 15:45:30 | +---------------------+----------------------+-------------------+
If you have a date but want a value in days, or vice versa,
use the TO_DAYS()
and
FROM_DAYS()
functions.
Date-and-time values also can be converted to days if you’re willing
to suffer loss of the time part.
TO_DAYS()
converts a
date to the corresponding number of days, and FROM_DAYS()
does the
opposite:
mysql>SELECT d,
->TO_DAYS(d) AS 'DATE to days',
->FROM_DAYS(TO_DAYS(d)) AS 'DATE to days to DATE'
->FROM date_val;
+------------+--------------+----------------------+ | d | DATE to days | DATE to days to DATE | +------------+--------------+----------------------+ | 1864-02-28 | 680870 | 1864-02-28 | | 1900-01-15 | 693975 | 1900-01-15 | | 1987-03-05 | 725800 | 1987-03-05 | | 1999-12-31 | 730484 | 1999-12-31 | | 2000-06-04 | 730640 | 2000-06-04 | +------------+--------------+----------------------+
When using TO_DAYS()
,
it’s best to stick to the advice of the MySQL Reference
Manual and avoid DATE
values that occur before the beginning of the Gregorian calendar
(1582). Changes in the lengths of calendar years and months prior to
that date make it difficult to speak meaningfully of what the value
of “day 0” might be. This differs from TIME_TO_SEC()
, where the
correspondence between a TIME
value and the resulting seconds value is obvious and has a
meaningful reference point of 0 seconds.
If you pass TO_DAYS()
a date-and-time value, it extracts the date part and discards the
time. This provides another means of extracting dates from DATETIME
and TIMESTAMP
values (in addition to those
already discussed in Extracting Parts of Dates or Times):
mysql>SELECT dt,
->TO_DAYS(dt) AS 'date part in days',
->FROM_DAYS(TO_DAYS(dt)) AS 'date part as DATE'
->FROM datetime_val;
+---------------------+-------------------+-------------------+ | dt | date part in days | date part as DATE | +---------------------+-------------------+-------------------+ | 1970-01-01 00:00:00 | 719528 | 1970-01-01 | | 1987-03-05 12:30:15 | 725800 | 1987-03-05 | | 1999-12-31 09:00:00 | 730484 | 1999-12-31 | | 2000-06-04 15:45:30 | 730640 | 2000-06-04 | +---------------------+-------------------+-------------------+ mysql>SELECT ts,
->TO_DAYS(ts) AS 'date part in days',
->FROM_DAYS(TO_DAYS(ts)) AS 'date part as DATE'
->FROM timestamp_val;
+---------------------+-------------------+-------------------+ | ts | date part in days | date part as DATE | +---------------------+-------------------+-------------------+ | 1970-01-01 00:00:00 | 719528 | 1970-01-01 | | 1987-03-05 12:30:15 | 725800 | 1987-03-05 | | 1999-12-31 09:00:00 | 730484 | 1999-12-31 | | 2000-06-04 15:45:30 | 730640 | 2000-06-04 | +---------------------+-------------------+-------------------+
For DATETIME
or TIMESTAMP
values that lie within the range
of the TIMESTAMP
data type (from
the beginning of 1970 through approximately 2037), 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 | | 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
UTC 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 interprets the
UNIX_TIMESTAMP()
argument
as a value in the client’s local time zone and converts it to UTC.
My server is in the U.S. Central time zone, six hours (21600
seconds) west of UTC.
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(),
->UNIX_TIMESTAMP(CURDATE()),
->FROM_UNIXTIME(UNIX_TIMESTAMP(CURDATE()))G
*************************** 1. row *************************** CURDATE(): 2006-05-30 UNIX_TIMESTAMP(CURDATE()): 1148965200 FROM_UNIXTIME(UNIX_TIMESTAMP(CURDATE())): 2006-05-30 00:00:00
52.14.239.155