How to use the MySQL PERIOD_DIFF() function

In this article, we will learn how to use the MySQL PERIOD_DIFF() function, which returns the number of months between two periods.

Posted on

In this article, we will learn how to use the MySQL PERIOD_DIFF() function, which returns the number of months between two periods. We will also see some examples of how to use this function in different situations, and explore some related functions that can be helpful for working with periods.

Syntax

The syntax of the PERIOD_DIFF() function is as follows:

PERIOD_DIFF(period1, period2)

The period1 and period2 parameters can be any valid period expressions, which are four or six-digit numbers that represent a year and a month. For example, 202312 represents the period of December 2023, and 202301 represents the period of January 2023. If either parameter is NULL, the function returns NULL. The PERIOD_DIFF() function returns an integer value that represents the number of months between the two periods. The value is positive if period1 is later than period2, and negative if period1 is earlier than period2. For example, PERIOD_DIFF(202312, 202301) returns 11, and PERIOD_DIFF(202301, 202312) returns -11.

Examples

Let’s see some examples of how to use the PERIOD_DIFF() function in MySQL.

Example 1: Get the number of months between two periods

We can use the PERIOD_DIFF() function to get the number of months between two periods. For example:

SELECT PERIOD_DIFF(202312, 202301) AS result;

This query will return the number of months between the periods of December 2023 and January 2023. The query will return 11, since there are 11 months between the two periods.

Example 2: Get the number of months between the current period and a given period

We can use the PERIOD_DIFF() function with the YEAR() and MONTH() functions, which return the year and month of a date or datetime value, respectively, to get the number of months between the current period and a given period. For example:

SELECT PERIOD_DIFF(YEAR(CURDATE()) * 100 + MONTH(CURDATE()), 202301) AS result;

This query will get the year and month of the current date, multiply the year by 100 and add the month, and then use the PERIOD_DIFF() function to get the number of months between the current period and the period of January 2023. For example, if the current date is 2023-12-15, the query will return 11, since there are 11 months between the current period and the period of January 2023.

Example 3: Get the number of months between two dates

We can use the PERIOD_DIFF() function with the DATE_FORMAT() function, which returns the date or datetime value formatted according to a specified format, to get the number of months between two dates. For example, suppose we want to get the number of months between the dates of 2023-05-12 and 2022-03-06. We can use the following query:

SELECT PERIOD_DIFF(DATE_FORMAT('2023-05-12', '%Y%m'), DATE_FORMAT('2022-03-06', '%Y%m')) AS result;

This query will format the two dates as period expressions, and then use the PERIOD_DIFF() function to get the number of months between them. The query will return 14, since there are 14 months between the two dates.

Example 4: Get the average number of months between the periods in a table

We can use the PERIOD_DIFF() function with the AVG() function, which returns the average value of a numeric column, to get the average number of months between the periods in a table. For example, suppose we have a table called projects that stores the details of some projects, with the following columns:

  • project_id: the unique identifier of the project
  • project_name: the name of the project
  • start_period: the period when the project started
  • end_period: the period when the project ended

We can use the following query to get the average number of months between the start and end periods of the projects:

SELECT AVG(PERIOD_DIFF(end_period, start_period)) AS average_duration
FROM projects;

This query will use the PERIOD_DIFF() function to get the number of months between the start and end periods of each project, and then use the AVG() function to get the average value of those numbers. For example, the query might return something like this:

average_duration
8.5

There are some other functions that are related to the PERIOD_DIFF() function, and can be useful for working with periods. Here are some of them:

  • PERIOD_ADD(): This function adds a number of months to a period and returns the resulting period. For example, PERIOD_ADD(202312, 3) returns 202403.
  • YEAR(): This function returns the year of a date or datetime value. For example, YEAR('2023-01-15') returns 2023.
  • MONTH(): This function returns the month of a date or datetime value. For example, MONTH('2023-01-15') returns 1.
  • CURDATE(): This function returns the current date as a date value. For example, CURDATE() returns ‘2023-12-15’ if the current date is 2023-12-15.
  • DATE_FORMAT(): This function returns the date or datetime value formatted according to a specified format. For example, DATE_FORMAT('2023-01-15', '%Y-%m-%d') returns ‘2023-01-15’.

Conclusion

In this article, we learned how to use the MySQL PERIOD_DIFF() function, which returns the number of months between two periods. We also saw some examples of how to use this function in different situations, and explored some related functions that can be helpful for working with periods.