A Short Interval - MySQL

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)