MySQL - PERIOD_DIFF() Function

MySQL PERIOD_DIFF() function is similar to the PERIOD_ADD() function. It is used to find the difference between two period values. In MySQL, a period is defined as a combination of year and month; and it will be represented in the format, YYMM or YYYYMM.

This function accepts two period values as parameter values, calculates the difference between them and returns the results (in the form of number of months).

A NULL value is returned if either of the arguments passed to the function are NULL.

Syntax

Following is the syntax of MySQL PERIOD_DIFF() function −

PERIOD_DIFF(P1,P2)

Parameters

This method accepts two parameters. The same is described below −

  • P1 is the value period value. The format is "YYMM" or "YYYYMM"

  • P2 is another period value that we want to subtract.

Return value

This function returns the difference in months between periods P1 and P2.

Example

In the following example, we are using the PERIOD_DIFF() function to calculate the difference between two given period values −

SELECT PERIOD_DIFF(202409, 202309) As Result;

Output

This will produce the following result −

Result
12

Example

Following is another example of this function −

SELECT PERIOD_DIFF('2405', '2305') As Result;

Output

Following is the output −

Result
12

Example

In this example, we have created a table named PLAYERS using the following CREATE TABLE query 

CREATE TABLE PLAYERS(
   ID int,
   NAME varchar(255),
   DATE_OF_BIRTH date,
   DOB_PERIOD varchar(30),
   Country varchar(255),
   PRIMARY KEY (ID)
);

Now, insert the following records into the ORDERS table using the INSERT statement −

INSERT INTO PLAYERS VALUES
(1, 'Shikhar Dhawan', DATE('1981-12-05'), 198112, 'India'),
(2, 'Jonathan Trott', DATE('1981-04-22'), 198104, 'SouthAfrica'),
(3, 'Kumara Sangakkara', DATE('1977-10-27'), 197710, 'Srilanka'),
(4, 'Virat Kohli', DATE('1988-11-05'), 198811, 'India'),
(5, 'Rohit Sharma', DATE('1987-04-30'), 198704, 'India'),
(6, 'Ravindra Jadeja', DATE('1988-12-06'), 198812, 'India'),
(7, 'James Anderson', DATE('1982-06-30'), 198206, 'England');

Execute the below query to fetch all the inserted records in the above-created table −

Select * From PLAYERS;

Following is the PLAYERS table −

ID NAME DATE_OF_BIRTH DOB_PERIOD COUNTRY
1 Shikhar Dhawan 1981-12-05 198112 India
2 Jonathan Trott 1981-04-22 198104 SouthAfrica
3 Kumara Sangakkara 1977-10-27 197710 Srilanka
4 Virat Kohli 1988-11-05 198811 India
5 Rohit Sharma 1987-04-30 198704 India
6 Ravindra Jadeja 1988-12-06 198812 India
7 James Anderson 1982-06-30 198206 England

Here, we are using the MySQL PERIOD_DIFF() function to calculate the difference in periods between "DOB_PERIOD" and the fixed period '197012' −

SELECT ID, NAME, DATE_OF_BIRTH, DOB_PERIOD, PERIOD_DIFF(DOB_PERIOD, 197012)
As Result From PLAYERS;

Output

The output is displayed as follows −

ID NAME DATE_OF_BIRTH DOB_PERIOD Result
1 Shikhar Dhawan 1981-12-05 198112 132
2 Jonathan Trott 1981-04-22 198104 124
3 Kumara Sangakkara 1977-10-27 197710 82
4 Virat Kohli 1988-11-05 198811 215
5 Rohit Sharma 1987-04-30 198704 196
6 Ravindra Jadeja 1988-12-06 198812 216
7 James Anderson 1982-06-30 198206 138
Advertisements