Home arrow MySQL arrow Page 6 - Date Arithmetic With MySQL

A Short Interval - 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.

  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



The PERIOD_DIFF() function is primarily used to calculate the number of months between two dates - as illustrated in the following example, which calculates the number of months between December 2002 and December 2003.

mysql> SELECT PERIOD_DIFF(200312, 200212);
| PERIOD_DIFF(200312, 200212) |
| 12 |
1 row in set (0.00 sec)

The values provided to the PERIOD_DIFF() function must be in the form YYYYMM or YYMM - the following statement is equivalent to the one above:

mysql> SELECT PERIOD_DIFF(0312, 0212);
| PERIOD_DIFF(0312, 0212) |
| 12 |
1 row in set (0.00 sec)

A corollary to the PERIOD_DIFF() function is the PERIOD_ADD() function, which adds a specified number of months to a date and displays the result. The first argument to PERIOD_ADD() is the start date, the second is the number of months to be added to it. Consider the following example, which adds 3 months to January 2003:

mysql> SELECT PERIOD_ADD(200301, 3);
| PERIOD_ADD(200301, 3) |
| 200304 |
1 row in set (0.02 sec)

You can even use the PERIOD_ADD() function to perform a subtraction operation, by specifying a negative integer as the second argument to PERIOD_ADD(). Consider the following example, which illustrates by subtracting 5 months from May 2003.

mysql> SELECT PERIOD_ADD(200305, -5);
| PERIOD_ADD(200305, -5) |
| 200212 |
1 row in set (0.00 sec)

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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- 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: