Date Arithmetic With MySQL - Counting Down (
Page 3 of 8 )
The first function in the list, the TO_DAYS() function, returns a number
corresponding to a specific date. This number is calculated as the number of
days elapsed between year 0 and the specified date. Consider the following
examples, which illustrate:
mysql> SELECT TO_DAYS('2003-04-06');
+-----------------------+
| TO_DAYS('2003-04-06') |
+-----------------------+
| 731676 |
+-----------------------+
1 row in set (0.04 sec)
The input value to the TO_DAYS() function may be a date in either
string
("YYYY-MM-DD") or numeric (YYYYMMDD) format. The following example is
equivalent to the one above:
mysql> SELECT TO_DAYS(20030406);
+-------------------+
| TO_DAYS(20030406) |
+-------------------+
| 731676 |
+-------------------+
1 row in set (0.01 sec)
You can obtain the current day number with the addition of the very
useful
NOW() command:
mysql> SELECT TO_DAYS(NOW());
+----------------+
| TO_DAYS(NOW()) |
+----------------+
| 731756 |
+----------------+
1 row in set (0.03 sec)
The number returned by the TO_DAYS() function can be easily converted back to
a human-readable date with the FROM_DAYS() function, which accepts a day number
and returns the corresponding date value. Consider the following examples, which
demonstrate:
mysql> SELECT FROM_DAYS(731756);
+-------------------+
| FROM_DAYS(731756) |
+-------------------+
| 2003-06-25 |
+-------------------+
1 row in set (0.01 sec)
mysql> SELECT FROM_DAYS(849302);
+-------------------+
| FROM_DAYS(849302) |
+-------------------+
| 2325-04-24 |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT FROM_DAYS(TO_DAYS('1999-05-14'));
+----------------------------------+
| FROM_DAYS(TO_DAYS('1999-05-14')) |
+----------------------------------+
| 1999-05-14 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> SELECT FROM_DAYS(0);
+--------------+
| FROM_DAYS(0) |
+--------------+
| 0000-00-00 |
+--------------+
1 row in set (0.00 sec)
As you may have guessed, the TO_DAYS() and FROM_DAYS() functions make it very
easy to execute the example alluded to in the introduction of this article -
adding 91 days to a date value and obtaining the resulting value
- since they
automatically adjust for the number of years in a specific month. Consider the
following examples, which illustrate by adding 1 day to the last day of February
in a leap and non-leap year:
mysql> SELECT FROM_DAYS(TO_DAYS('2004-02-28') + 1);
+--------------------------------------+
| FROM_DAYS(TO_DAYS('2004-02-28') + 1) |
+--------------------------------------+
| 2004-02-29 |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT FROM_DAYS(TO_DAYS('2003-02-28') + 1);
+--------------------------------------+
| FROM_DAYS(TO_DAYS('2003-02-28') + 1) |
+--------------------------------------+
| 2003-03-01 |
+--------------------------------------+
1 row in set (0.00 sec)
It's important to note that the TO_DAYS() and FROM_DAYS() functions do not
support dates preceding the year 1582. In case you're wondering why, that was
the year Pope Gregory XIII introduced the modern Gregorian calendar to replace
the previous Julian calendar. As a result of switching calendars, many countries
"lost" 10 or more days. The TO_DAYS() and FROM_DAYS() do not take into account
these lost days, and so will return inaccurate results for such dates - as
clearly illustrated in the examples below:
mysql> SELECT TO_DAYS('0000-00-00');
+-----------------------+
| TO_DAYS('0000-00-00') |
+-----------------------+
| NULL |
+-----------------------+
1 row in set (0.01 sec)
mysql> SELECT TO_DAYS('0001-01-01');
+-----------------------+
| TO_DAYS('0001-01-01') |
+-----------------------+
| 730851 |
+-----------------------+
1 row in set (0.01 sec)
/ 35 
