PERIOD_DIFF() Function Examples – MySQL

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.

You may like

  1. TIMESTAMP() Function Examples – MySQL
  2. TIMEDIFF() Function Examples – MySQL
  3. SEC_TO_TIME() Function Examples – MySQL
  4. QUARTER() Function Examples – MySQL
  5. STR_TO_DATE() Function Examples – MySQL
  6. PERIOD_ADD() Function Examples – MySQL
  7. SECOND() Function Examples – MySQL
  8. MICROSECOND() Function Examples – MySQL
  9. MAKETIME() Function Examples – MySQL
  10. Localtime() Function Examples – MySQL
  11. CURTIME() Function Examples – MySQL
  12. MySQL CURRENT TIMESTAMP Function Examples
  13. MySQL CURRENT TIME Function Examples
  14. ADDTIME() Function Examples – MySQL
  15. MINUTE() Function Examples – MySQL
  16. HOUR() Function Examples – MySQL
  17. NOW() MySQL Function | MySQL
  18. MySQL DATE() And TIME() Functions (List)
  19. TIMESTAMPADD() Function Examples – MySQL
  20. TIMESTAMPDIFF() Function Examples – MySQL

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

AuthorAdmin

My name is Devendra Dode. I am a full-stack developer, entrepreneur, and owner of Tutsmake.com. I like writing tutorials and tips that can help other developers. I share tutorials of PHP, Javascript, JQuery, Laravel, Livewire, Codeigniter, Vue JS, Angular JS, React Js, WordPress, and Bootstrap from a starting stage. As well as demo example.

Leave a Reply

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