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