MySQL - PERIOD_ADD() 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_ADD() function accepts a period and a numerical value representing the number of months and adds the specified number of months to the given period. The result of this function will be in the form of YYYYMM.

Syntax

Following is the syntax of the above function –

PERIOD_ADD(P,N);

Where,

• P is the value period value.

• N is the number of months you need to add to the period.

Example 1

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

mysql> SELECT PERIOD_ADD('201509', 12);
+--------------------------+
| PERIOD_ADD('201509', 12) |
+--------------------------+
|                   201609 |
+--------------------------+
1 row in set (0.04 sec)
mysql> SELECT PERIOD_ADD('199611', 16);
+--------------------------+
| PERIOD_ADD('199611', 16) |
+--------------------------+
|                   199803 |
+--------------------------+
1 row in set (0.00 sec)

Example 2

Following is another example of this function –

mysql> SELECT PERIOD_ADD('1805',112);
+------------------------+
| PERIOD_ADD('1805',112) |
+------------------------+
|                 202709 |
+------------------------+
1 row in set (0.00 sec)
mysql> SELECT PERIOD_ADD('1011',35);
+-----------------------+
| PERIOD_ADD('1011',35) |
+-----------------------+
|                201310 |
+-----------------------+
1 row in set (0.00 sec)

Example 3

We can also pass negative values as arguments to this function –

mysql> SELECT PERIOD_ADD('177912',-35);
+--------------------------+
| PERIOD_ADD('177912',-35) |
+--------------------------+
|                   177701 |
+--------------------------+
1 row in set (0.00 sec)
mysql> SELECT PERIOD_ADD('202101',-144);
+---------------------------+
| PERIOD_ADD('202101',-144) |
+---------------------------+
|                    200901 |
+---------------------------+
1 row in set (0.00 sec)
mysql> SELECT PERIOD_ADD('9611', -09);
+-------------------------+
| PERIOD_ADD('9611', -09) |
+-------------------------+
|                  199602 |
+-------------------------+
1 row in set (0.00 sec)

Example 4

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 adds 22 months to the entities of the DOBPeriod column —

mysql> SELECT First_Name, Last_Name, Date_Of_Birth, PERIOD_ADD(DOBPeriod, 22) from PlayerData;
+------------+------------+---------------+---------------------------+
| First_Name | Last_Name  | Date_Of_Birth | PERIOD_ADD(DOBPeriod, 22) |
+------------+------------+---------------+---------------------------+
| Shikhar    | Dhawan     | 1981-12-05    | 198310                    |
| Jonathan   | Trott      | 1981-04-22    | 198302                    |
| Kumara     | Sangakkara | 1977-10-27    | 197908                    |
| Virat      | Kohli      | 1988-11-05    | 199009                    |
| Rohit      | Sharma     | 1987-04-30    | 198902                    |
| Ravindra   | Jadeja     | 1988-12-06    | 199010                    |
| James      | Anderson   | 1982-06-30    | 198404                    |
+------------+------------+---------------+---------------------------+
7 rows in set (0.00 sec)

Example 5

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 adds 11 months to the entities of the DispatchPeriod column —

mysql> SELECT ProductName, CustomerName, DispatchPeriod, Price, PERIOD_ADD(DispatchPeriod, 11) FROM SalesData;
+-------------+--------------+----------------+-------+--------------------------------+
| ProductName | CustomerName | DispatchPeriod | Price | PERIOD_ADD(DispatchPeriod, 11) |
+-------------+--------------+----------------+-------+--------------------------------+
| Key-Board   | Raja         | 201909         | 7000  | 202008                         |
| Earphones   | Roja         | 201905         | 2000  | 202004                         |
| Mouse       | Puja         | 201903         | 3000  | 202002                         |
| Mobile      | Vanaja       | 201903         | 9000  | 202002                         |
| Headset     | Jalaja       | 201904         | 6000  | 202003                         |
+-------------+--------------+----------------+-------+--------------------------------+
5 rows in set (0.00 sec)

Example 6

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 adds 19 months to the subscription period for all the records in the above table —

mysql> SELECT SubscriberName, PackageName, SubscriptionPeriod, PERIOD_ADD(SubscriptionPeriod, 19) FROM SubscribersList;
+----------------+-------------+--------------------+------------------------------------+
| SubscriberName | PackageName | SubscriptionPeriod | PERIOD_ADD(SubscriptionPeriod, 19) |
+----------------+-------------+--------------------+------------------------------------+
| Rama           | Premium     | 202010             |                             202205 |
| Robert         | Basic       | 202011             |                             202206 |
| Rahman         | Moderate    | 202103             |                             202210 |
| Radha          | Basic       | 202102             |                             202209 |
| Rajiya         | Premium     | 202101             |                             202208 |
+----------------+-------------+--------------------+------------------------------------+
5 rows in set (0.00 sec)