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.


The syntax of the period_diff() function is:


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

List of Examples

See the list of examples below:


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

SELECT PERIOD_DIFF(201906, 201905);


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


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

 SELECT PERIOD_DIFF(201905, 201907); 


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

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


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



| PERIOD_DIFF(1906, 1905) |
|                       1 |


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

    CURDATE( ) AS 'Current Date',
    201010 AS 'Previous Period',


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


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;


MySQL period_diff() function example query


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.


My name is Devendra Dode. I am a full-stack developer, entrepreneur, and owner of 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 *