How the PERIOD_DIFF() function works in Mariadb?

The PERIOD_DIFF() function is a built-in function in Mariadb that returns the number of months between two periods. A period is a year and a month, represented by a four-digit number. For example, 202101 is the period of January 2021. The function is useful for calculating the duration or difference between two time periods.

Syntax

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

PERIOD_DIFF(period1, period2)

Where period1 and period2 are four-digit numbers that represent periods. If period1 or period2 is NULL, the function returns NULL.

Examples

Example 1: Calculating the difference between two periods

The following example shows how to use the PERIOD_DIFF() function to calculate the difference between two periods:

SELECT PERIOD_DIFF(202101, 202004) AS Diff;

The output is:

+------+
| Diff |
+------+
|    9 |
+------+

The function returns 9, which is the number of months between the period of January 2021 and the period of April 2020.

Example 2: Calculating the duration of a period

The following example shows how to use the PERIOD_DIFF() function to calculate the duration of a period:

SELECT PERIOD_DIFF(202101, 202100) AS Duration;

The output is:

+----------+
| Duration |
+----------+
|        1 |
+----------+

The function returns 1, which is the number of months in the period of January 2021. The function subtracts the period of December 2020 (202100) from the period of January 2021 (202101) to get the duration.

Related Functions

There are some other functions in Mariadb that are related to the PERIOD_DIFF() function. They are:

• PERIOD_ADD(): This function returns a period that is a number of months away from a given period. A period is a year and a month, represented by a four-digit number. For example, 202101 is the period of January 2021. The function is useful for adding or subtracting months from a time period.
• DATEDIFF(): This function returns the number of days between two dates. The function is useful for calculating the elapsed time or difference between two dates.
• TIMESTAMPDIFF(): This function returns the difference between two timestamps in a specified unit. The function allows you to specify the unit of the difference, such as second, minute, hour, day, week, month, year, etc. The function is useful for calculating the interval or difference between two timestamps.

Conclusion

The PERIOD_DIFF() function is a useful function in Mariadb that allows you to calculate the number of months between two periods. A period is a year and a month, represented by a four-digit number. The function is useful for calculating the duration or difference between two time periods. You can also use other functions like PERIOD_ADD(), DATEDIFF(), and TIMESTAMPDIFF() to manipulate time periods in different ways. I hope this article helped you understand how the PERIOD_DIFF() function works in Mariadb.