MySQL PERIOD_DIFF() Function

Summary: in this tutorial, you will learn how to use the MySQL PERIOD_DIFF() function to calculate the number of months between two periods.

Introduction to MySQL PERIOD_DIFF() function

The PERIOD_DIFF() function calculates the number of months between two periods in the format YYMM or YYYYMM.

Here’s the syntax of the PERIOD_DIFF() function:

PERIOD_DIFF(P1, P2)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• P1: The first period (in the format YYMM or YYYYMM).
• P2: The second period (in the format YYMM or YYYYMM).

The PERIOD_DIFF() function returns the number of months between these two periods.

If either P1 or P2 is NULL, the PERIOD_DIFF() function returns NULL.

MySQL PERIOD_DIFF() function examples

Let’s take some examples of using the PERIOD_DIFF() function.

1) Calculating the Difference Between Two Periods

Let’s start with a basic example:

SELECT PERIOD_DIFF(200802, 200703);Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------------------+
| PERIOD_DIFF(200802, 200703) |
+-----------------------------+
|                          11 |
+-----------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

In this example, we used the PERIOD_DIFF() to calculate the difference between the periods '200802' and '200703', which is 11 months.

2) Using PERIOD_ADD() function with NULL values

The PERIOD_ADD() function returns NULL if either argument is NULL. For example:

SELECT PERIOD_DIFF(NULL, '202112');Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------------------+
| PERIOD_DIFF(NULL, '202112') |
+-----------------------------+
|                        NULL |
+-----------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

3) Using PERIOD_DIFF() in a real-world application

Suppose you have a database with information about loans. And you want to calculate the loan duration in months based on the disbursement and maturity periods.

First, create a table to store loan data:

CREATE TABLE loans (
    loan_id INT AUTO_INCREMENT PRIMARY KEY,
    disbursement_period VARCHAR(6),
    maturity_period VARCHAR(6)
);Code language: SQL (Structured Query Language) (sql)

The loans table stores loan data with disbursement and maturity periods in the format YYYYMM.

Second, insert some rows into the loans table:

INSERT INTO loans (disbursement_period, maturity_period) 
VALUES
    ('202201', '202401'),
    ('202305', '202505'),
    ('202112', '202306');Code language: SQL (Structured Query Language) (sql)

Third, calculate the loan duration for each loan using PERIOD_DIFF() function:

SELECT 
  disbursement_period, 
  maturity_period, 
  PERIOD_DIFF(
    maturity_period, disbursement_period
  ) AS loan_duration_months 
FROM 
  loans;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------+-----------------+----------------------+
| disbursement_period | maturity_period | loan_duration_months |
+---------------------+-----------------+----------------------+
| 202201              | 202401          |                   24 |
| 202305              | 202505          |                   24 |
| 202112              | 202306          |                   18 |
+---------------------+-----------------+----------------------+
3 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The query uses the PERIOD_DIFF() function to calculate the loan duration in months for each loan.

Summary

• Use PERIOD_DIFF() function to calculate the difference in months between two periods represented in the format YYMM or YYYYMM.