Logo         Community
  Trog
Services
The Company
Community
Columns
Your Account
Contact Us
 
 
Date Arithmetic With MySQL
Simplify date and time arithmetic with built-in MySQL functions.

| Counting Down |

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)
[/code]

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)



How to do Everything with PHP & MySQL
How to do Everything with PHP & MySQL, the best-selling book by Melonfire, explains how to take full advantage of PHP's built-in support for MySQL and link the results of database queries to Web pages. You'll get full details on PHP programming and MySQL database development, and then you'll learn to use these two cutting-edge technologies together. Easy-to-follow sample applications include a PHP online shopping cart, a MySQL order tracking system, and a PHP/MySQL news publishing system.

Read more, or grab your copy now!


previous page more like this  print this article  next page
 
Search...
 
In trog...
Logging With PHP
Building A Quick-And-Dirty PHP/MySQL Publishing System
Output Buffering With PHP
Date/Time Processing With PHP
Creating Web Calendars With The PEAR Calendar Class
more...
 
In the hitg report...
Crime Scenes
Animal Attraction
Lord Of The Strings
more...
 
In boombox...
Patience - George Michael
Think Tank - Blur
My Private Nation - Train
more...
 
In colophon...
Hostage - Robert Crais
The Dead Heart - Douglas Kennedy
Right As Rain - George Pelecanos
more...
 
In cut!...
American Chai
The Core
Possession
more...
 
Find out how you can use this article on your own Web site!


Copyright © 1998-2018 Melonfire. All rights reserved
Terms and Conditions | Feedback