Database Reference
In-Depth Information
->
FROM datetime_val;
+---------------------+----------------------+-------------------+
| dt | time part in seconds | time part as TIME |
+---------------------+----------------------+-------------------+
| 1970-01-01 00:00:00 | 0 | 00:00:00 |
| 1999-12-31 09:00:00 | 32400 | 09:00:00 |
| 2000-06-04 15:45:30 | 56730 | 15:45:30 |
| 2017-03-16 12:30:15 | 45015 | 12:30:15 |
+---------------------+----------------------+-------------------+
Converting between dates and days
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 can
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 |
| 1999-12-31 | 730484 | 1999-12-31 |
| 2000-06-04 | 730640 | 2000-06-04 |
| 2017-03-16 | 736769 | 2017-03-16 |
+------------+--------------+----------------------+
When using
TO_DAYS()
, it's best to stick to the advice of the
MySQL Reference Man‐
ual
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 sec‐
onds 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
(or
TIMESTAMP
)
values, in addition to those already discussed in
Recipe 6.8
:
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 |
+---------------------+-------------------+-------------------+
