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 formatYYMM
orYYYYMM
).P2
: The second period (in the formatYYMM
orYYYYMM
).
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 formatYYMM
orYYYYMM
.