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.
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)
/ 35 
