MySQL PERIOD_DIFF() Function

MySQL PERIOD_DIFF() function; In this tutorial, we will learn how to use the period_diff() function of MySQL with the help of useful examples.

And as well as, will use the test database table and calculate the difference between created date and update date using the period_diff() function; As shown in below examples.

Mysql PERIOD_DIFF() Function

In MySQL, the PERIOD_DIFF () function finds the difference between the two periods.

In other word, The MySQL PERIOD_DIFF() function is used to calculate the difference between two periods

Note: Periods are provided as two separate arguments, and they should be in YYMM or YYYMM format.

Syntax

The syntax of the period_diff() function is:

PERIOD_DIFF(P1,P2)

Here: P1 is the first period, and P2 is the second.

List of Examples

See the list of examples below:

Example-1

Let’s take first example of this period_diff() mysql function is:

SELECT PERIOD_DIFF(201906, 201905);

Output-1

+-----------------------------+
| PERIOD_DIFF(201906, 201905) |
+-----------------------------+
|                           1 |
+-----------------------------+

We have taken two periods the first period is 2019-06 and the second is 2019-05. The PERIOD_DIFF() function has calculated 1 month’s difference between given periods.

Example-2

Let’s take the second example of period_diff() function is below:

 SELECT PERIOD_DIFF(201905, 201907);

Output-2

+-----------------------------+
| PERIOD_DIFF(201905, 201907) |
+-----------------------------+
|                          -2 |
+-----------------------------+

The above example returns the negative value. Because we have swapped the given periods.

Example-3

We take another example of this, using the two digit year format like YYMM. Below an example to demonstrate.

SELECT PERIOD_DIFF(1906, 1905);

Output-3

+-------------------------+
| PERIOD_DIFF(1906, 1905) |
+-------------------------+
|                       1 |
+-------------------------+

Example-4

Now we take the next example with the current date. Below an example for demostration:

    SELECT 
    CURDATE( ) AS 'Current Date',
    EXTRACT(YEAR_MONTH FROM CURDATE( )) AS 'Current Period',
    201010 AS 'Previous Period',
    PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM CURDATE( )), 201010) AS 'Difference';

Output-4

+--------------+----------------+-----------------+------------+
| Current Date | Current Period | Previous Period | Difference |
+--------------+----------------+-----------------+------------+
|  2019-07-20  |         201907 |          201010 |       105  |
+--------------+----------------+-----------------+------------+

Example-5

Let’s take a database example using the period_diff () function. For example, we have a test table in the database, using this table, we will calculate the difference between create_date and updated_date. Using this example, we will calculate the difference between the table column update time

SELECT created_at AS 'Create Date', updated_at as 'Update Date', 
PERIOD_DIFF( EXTRACT(YEAR_MONTH FROM updated_at), EXTRACT(YEAR_MONTH FROM created_at) ) AS 'Update Diffrence In Month' 
From test;

Output-5

MySQL period_diff() function example query

Conclusion

Here, you have learned how to use MySQL PERIOD_DIFF() function with various examples.