5.12 Converting Between Dates and Days
5.12.1 Problem
You have a date but want a value in
days, or vice versa.
5.12.2 Solution
DATE values can be converted to and from days with
TO_DAYS( ) and FROM_DAYS(
).
Date-and-time values also can be converted to days if
you're willing to suffer loss of the time part.
5.12.3 Discussion
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
probably 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:
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 |
+----------------+-------------------+-------------------+
| 19700101000000 | 719528 | 1970-01-01 |
| 19870305123015 | 725800 | 1987-03-05 |
| 19991231090000 | 730484 | 1999-12-31 |
| 20000604154530 | 730640 | 2000-06-04 |
+----------------+-------------------+-------------------+
|
|
|
