MySQL PERIOD_DIFF() – With Easy Examples

In this tutorial, we will study the MySQL PERIOD_DIFF() function. In the last article, we saw the MySQL PERIOD_ADD() function. Now, you must be wondering, is there a function to subtract periods?

Well there is one!

Suppose you have a table in which you store the details of the invoices you have to pay- Id, Name of the beneficiary, the amount to be paid, and the period in which the amount should be paid.

Now suppose you get some money in a COVID-19 government relief scheme and you are able to pay all these bills way before time. You want to see how many months early you made each payment. This is where we use the PERIOD_DIFF() function.

The MySQL PERIOD_DIFF() function is used to return the difference between two periods. The result returned is the number of months between those periods.

A period is a value of the format YYYYMM or YYMM where the Ys stand for the year value and Ms stand for the month value.

When we have two Ys, then the last two digits of the year are used. An example of period values is 202104. Here 2021 is the year and 04 is the month. In the YYMM format, this will be written as 2104. Let us see the syntax and examples of the PERIOD_DIFF() function now.

Syntax for MySQL PERIOD_DIFF()

PERIOD_DIFF(period1, period2)Code language: SQL (Structured Query Language) (sql)

Where, ‘period1’ and ‘period2’ are period values and MySQL will return the result of period1-period2.

Examples of MySQL PERIOD_DIFF()

Let us start with a couple of basic examples. In our first query, we will find the difference between the periods 202104 and 202101. In our second query, let’s take a bigger range. We will find the difference between 202102 and 199902. We will use the SELECT statement and an alias called Result. The queries are –

SELECT PERIOD_DIFF(202104, 202101) AS Result; 
SELECT PERIOD_DIFF(202102, 199902) AS Result; Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL PERIOD_DIFF() Can Return Negative Values

If the ‘period1’ value is greater than ‘period2’ value, then MySQL PERIOD_DIFF() will return a negative answer as result. Let us demonstrate this using the below queries.

SELECT PERIOD_DIFF(202101, 202103) AS Result; 
SELECT PERIOD_DIFF(201201, 202103) AS Result; Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL PERIOD_DIFF() With Double Digit Year Values

As I mentioned in the beginning, we can also pass the period value in the YYMM format to PERIOD_DIFF(). Let us see a couple of examples of this. Consider the below queries.

SELECT PERIOD_DIFF(1905, 1512) AS Result; 
SELECT PERIOD_DIFF(1710, 0604) AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is –

Miscellaneous Example

Let us find the number of years between the period 202108 and 194708 using the PERIOD_DIFF() function. 15th August 1947 is India’s Independence Day. The period value for it is 194708.

Wait, number of years using PERIOD_DIFF()? Well, since PERIOD_DIFF() returns the output in months, we can divide it by 12 to get a number of years’ value. Let us see the query for this.

SELECT PERIOD_DIFF(202108,194708)/12 AS 'Years Since Independence';Code language: SQL (Structured Query Language) (sql)

And the output is –

Working With Tables

Consider the below Invoices table.

Now let us consider the situation I mentioned in the beginning. Suppose you have a table in which you store the details of the invoices you have to pay- Id, Name of the beneficiary, the amount to be paid, and the period in which the amount should be paid. Now suppose you get some money in a COVID-19 government relief scheme and you are able to pay all these bills way before time. You want to see how many months early you made each payment. Let us make use of the PERIOD_DIFF() function. Consider the below query.

SELECT Id, Name, Amount, PaymentPeriod, PERIOD_DIFF(PaymentPeriod, 202101) AS 'X Months From Now' FROM Invoices; Code language: SQL (Structured Query Language) (sql)

And the output is –

Conclusion

Addition and subtraction operations on period values are very important and have a plethora of use cases. That is why, the PERIOD_DIFF() function is so important. I would encourage you to practice some more queries with this function.

References

• MySQL Official Documentation on the PERIOD_DIFF() function.