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.

Table of Contents

## Syntax for MySQL PERIOD_DIFF()

```
PERIOD_DIFF(period1, period2)
```

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;
```

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;
```

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;
```

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';
```

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;
```

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.