PERIOD_DIFF() Function Examples – MySQL

Here, we will discuss how to works the period_diff() function of MySQL. How we can use this function. We will take various examples of period_diff() and demonstrate to you.

Here we will use the test database table and calculate the difference between created date and update date using the period_diff() function. Also, we will provide the MySQL query for period_diff().

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.L

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.

If you have any questions or thoughts to share, use the comment form below to reach us.

Spread the love

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *