Home arrow MySQL arrow Page 3 - Date Arithmetic With MySQL

Counting Down - MySQL

When dealing with date and time values, one of the more common (and complex) tasks involves performing addition and subtraction operations on these values. However, with MySQL's powerful date and time API taking care of all the minor adjustments for you, manipulating date and time data is no longer the tedious and time-consuming process it used to be. Find out why, inside.

TABLE OF CONTENTS:
  1. Date Arithmetic With MySQL
  2. When Two And Two Don't Make Four
  3. Counting Down
  4. The Number Game
  5. Artificial Intelligence
  6. A Short Interval
  7. Lather, Rinse, Repeat
  8. Code Poet
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 35
July 03, 2003

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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)



 
 
>>> More MySQL Articles          >>> More By icarus, (c) Melonfire
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: