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.