MySQL - PERIOD_DIFF() Function

---

The DATE, DATETIME and TIMESTAMP datatypes in MySQL are used to store the date, date and time, time stamp values respectively. Where a time stamp is a numerical value representing the number of milliseconds from '1970-01-01 00:00:01' UTC (epoch) to the specified time. MySQL provides a set of functions to manipulate these values.

A period is a combination of year and month it will be in the format YYMM or, YYYYMM.

The MYSQL PERIOD_DIFF() function accepts two period values as parameters, finds the difference between them and returns the results (number of months).

Syntax

Following is the syntax of the above function –

PERIOD_DIFF(P1,P2)

Example 1

Following example demonstrates the usage of the PERIOD_DIFF() function –

mysql> SELECT PERIOD_DIFF('201509', 102109);
+-------------------------------+
| PERIOD_DIFF('201509', 102109) |
+-------------------------------+
|                         11928 |
+-------------------------------+
1 row in set (0.00 sec)
mysql> SELECT PERIOD_DIFF(199611, 201911);
+-----------------------------+
| PERIOD_DIFF(199611, 201911) |
+-----------------------------+
|                        -276 |
+-----------------------------+
1 row in set (0.00 sec)

Example 2

Following is another example of this function –

mysql> SELECT PERIOD_DIFF('180508', '201902'); 
+---------------------------------+
| PERIOD_DIFF('180508', '201902') |
+---------------------------------+
|                           -2562 |
+---------------------------------+
1 row in set (0.00 sec)
mysql> SELECT PERIOD_DIFF('101109',201308);
+------------------------------+
| PERIOD_DIFF('101109',201308) |
+------------------------------+
|                       -12023 |
+------------------------------+
1 row in set (0.00 sec)

Example 3

You can also pass the column name as an argument to this function. Let us create a table with name PlayerData in MySQL database using CREATE statement as shown below –

mysql> CREATE TABLE PlayerData(
	First_Name VARCHAR(255),
	Last_Name VARCHAR(255),
	Date_Of_Birth date,
	DOBPeriod VARCHAR(30),
	Country VARCHAR(255)
);

Now, we will insert 7 records in PlayerData table using INSERT statements −

mysql> insert into PlayerData values('Shikhar', 'Dhawan', DATE('1981-12-05'),'198112', 'India');
mysql> insert into PlayerData values('Jonathan', 'Trott', DATE('1981-04-22'), '198104', 'SouthAfrica');
mysql> insert into PlayerData values('Kumara', 'Sangakkara', DATE('1977-10-27'), '197710', 'Srilanka');
mysql> insert into PlayerData values('Virat', 'Kohli', DATE('1988-11-05'), '198811', 'India');
mysql> insert into PlayerData values('Rohit', 'Sharma', DATE('1987-04-30'), '198704', 'India');
mysql> insert into PlayerData values('Ravindra', 'Jadeja', DATE('1988-12-06'), '198812', 'India');
mysql> insert into PlayerData values('James', 'Anderson', DATE('1982-06-30'), '198206', 'England');

Following query subtracts the period 201206 from values of the DOBPeriod column—

mysql> SELECT First_Name, Last_Name, Date_Of_Birth, PERIOD_DIFF(DOBPeriod, 201206) from PlayerData;
+------------+------------+---------------+--------------------------------+
| First_Name | Last_Name  | Date_Of_Birth | PERIOD_DIFF(DOBPeriod, 201206) |
+------------+------------+---------------+--------------------------------+
| Shikhar    | Dhawan     | 1981-12-05    |                          -366  |
| Jonathan   | Trott      | 1981-04-22    |                           -374 |
| Kumara     | Sangakkara | 1977-10-27    |                           -416 |
| Virat      | Kohli      | 1988-11-05    |                           -283 |
| Rohit      | Sharma     | 1987-04-30    |                           -302 |
| Ravindra   | Jadeja     | 1988-12-06    |                           -282 |
| James      | Anderson   | 1982-06-30    |                           -360 |
+------------+------------+---------------+--------------------------------+
7 rows in set (0.11 sec)

Example 4

Let us create another table with name SalesData in MySQL database using CREATE statement as follows –

mysql> CREATE TABLE SalesData(
	ID INT,
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchPeriod VARCHAR(30),
	Price INT,
	Location VARCHAR(255)
);
Query OK, 0 rows affected (2.22 sec)

Now, we will insert 5 records in SalesData table using INSERT statements −

insert into SalesData values (1, 'Key-Board', 'Raja', '201909', 7000, 'Hyderabad');
insert into SalesData values (2, 'Earphones', 'Roja', '201905', 2000, 'Vishakhapatnam');
insert into SalesData values (3, 'Mouse', 'Puja', '201903', 3000, 'Vijayawada');
insert into SalesData values (4, 'Mobile', 'Vanaja', '201903', 9000, 'Chennai');
insert into SalesData values (5, 'Headset', 'Jalaja', '201904', 6000, 'Goa');

Following query subtracts 10 months from the entities of the DispatchPeriod column —

mysql> SELECT ProductName, CustomerName, DispatchPeriod, Price, PERIOD_DIFF(DispatchPeriod, '000010') FROM SalesData;
+-------------+--------------+----------------+-------+---------------------------------------+
| ProductName | CustomerName | DispatchPeriod | Price | PERIOD_DIFF(DispatchPeriod, '000010') |
+-------------+--------------+----------------+-------+---------------------------------------+
| Key-Board   | Raja         | 201909         | 7000  |                                   227 |
| Earphones   | Roja         | 201905         | 2000  |                                   223 |
| Mouse       | Puja         | 201903         | 3000  |                                   221 |
| Mobile      | Vanaja       | 201903         | 9000  |                                   221 |
| Headset     | Jalaja       | 201904         | 6000  |                                   222 |
+-------------+--------------+----------------+-------+---------------------------------------+
5 rows in set (0.07 sec)

Example 5

Suppose we have created a table named SubscribersList with 5 records in it using the following queries –

mysql> CREATE TABLE SubscribersList (
	SubscriberName VARCHAR(255),
	PackageName VARCHAR(255),
	SubscriptionPeriod VARCHAR(30)
);
insert into SubscribersList values('Rama', 'Premium', '202010');
insert into SubscribersList values('Robert', 'Basic', '202011');
insert into SubscribersList values('Rahman', 'Moderate', '202103');
insert into SubscribersList values('Radha', 'Basic', '202102');
insert into SubscribersList values('Rajiya', 'Premium', '202101');

Following query subtracts 10 years and 3 months from the subscription period for all the records in the above table —

mysql> SELECT SubscriberName, PackageName, SubscriptionPeriod, PERIOD_DIFF(SubscriptionPeriod, '001003') FROM SubscribersList;
+----------------+-------------+--------------------+-------------------------------------------+
| SubscriberName | PackageName | SubscriptionPeriod | PERIOD_DIFF(SubscriptionPeriod, '001003') |
+----------------+-------------+--------------------+-------------------------------------------+
| Rama           | Premium     | 202010             |                                       127 |
| Robert         | Basic       | 202011             |                                       128 |
| Rahman         | Moderate    | 202103             |                                       132 |
| Radha          | Basic       | 202102             |                                       131 |
| Rajiya         | Premium     | 202101             |                                       130 |
+----------------+-------------+--------------------+-------------------------------------------+
5 rows in set (0.00 sec)