PERIOD_DIFF() Examples – MySQL

Posted on by Ian

MySQL has a PERIOD_DIFF() function that enables you to find the difference between two periods. The periods are provided as two separate arguments, and they should in the format YYMM or YYYYMM.

Syntax

The syntax goes like this:

PERIOD_DIFF(P1,P2)

Where P1 is the first period, and P2 is the second.

Note that although this function appears to use dates, the period arguments are not actually date values.

Example 1 – Basic Usage

Here’s a basic example.

SELECT PERIOD_DIFF(199906, 199905);

Result:

+-----------------------------+
| PERIOD_DIFF(199906, 199905) |
+-----------------------------+
|                           1 |
+-----------------------------+

So in this case, there’s 1 month’s difference between the two periods.

Example 2 – Negative Results

Here’s what happens if we swap those two periods around.

SELECT PERIOD_DIFF(199905, 199906);

Result:

+-----------------------------+
| PERIOD_DIFF(199905, 199906) |
+-----------------------------+
|                          -1 |
+-----------------------------+

As expected, the result is a negative value.

Example 3 – Two Digit Years

You can also use two digit years (so that the format is YYMM). Here’s an example to demonstrate.

SELECT PERIOD_DIFF(9906, 9905);

Result:

+-------------------------+
| PERIOD_DIFF(9906, 9905) |
+-------------------------+
|                       1 |
+-------------------------+

Example 4 – Using the Current Date

Here’s an example that derives one of the period arguments from the current date. That period is then compared to a fixed period.

SELECT 
    CURDATE( ) AS 'Current Date',
    EXTRACT(YEAR_MONTH FROM CURDATE( )) AS 'Current Period',
    199901 AS 'Previous Period',
    PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM CURDATE( )), 199901) AS 'Difference';

Result:

+--------------+----------------+-----------------+------------+
| Current Date | Current Period | Previous Period | Difference |
+--------------+----------------+-----------------+------------+
| 2018-06-30   |         201806 |          199901 |        233 |
+--------------+----------------+-----------------+------------+

Example 5 – A Database Example

Here’s an example that takes the previous example a step further and compares the current period with a period derived from a date in a database.

USE sakila;
SELECT
    EXTRACT(YEAR_MONTH FROM CURDATE( )) AS 'Current Period',
    payment_date AS 'Payment Date',
    EXTRACT(YEAR_MONTH FROM payment_date) AS 'Payment Period',
    PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM payment_date), EXTRACT(YEAR_MONTH FROM CURDATE( ))) AS 'Difference'
FROM payment
WHERE payment_id = 1;

Result:

+----------------+---------------------+----------------+------------+
| Current Period | Payment Date        | Payment Period | Difference |
+----------------+---------------------+----------------+------------+
|         201806 | 2005-05-25 11:30:37 |         200505 |       -157 |
+----------------+---------------------+----------------+------------+