MariaDB PERIOD_DIFF() Function

In MariaDB, PERIOD_DIFF() is a built-in function that returns the number of months between two periods specified by year and month.

MariaDB PERIOD_DIFF() Syntax

This is the syntax of the MariaDB PERIOD_DIFF() function:

PERIOD_DIFF(period1, period2)

Parameters

period1

Required. Format: YYYYMM or YYMM.

period2

Required. Format: YYYYMM or YYMM.

If you supply the wrong number of arguments, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'PERIOD_DIFF'.

Return value

The MariaDB PERIOD_DIFF() function returns the number of months between two periods specified by year and month.

If period1 later than period2, PERIOD_DIFF() will return a a positive number, otherwise it will return a negative number.

For two-digit years, MariaDB handles them as follows:

  • Values ​​from 00 to 69 will be converted 2000 to 2069
  • Values ​​from 70 to 99 will be converted 1970 to 1999

MariaDB PERIOD_DIFF() Examples

Example 1

The following statement shows the basic usage of the MariaDB PERIOD_DIFF() function:

SELECT PERIOD_DIFF(202408, 202302);

Output:

+-----------------------------+
| PERIOD_DIFF(202408, 202302) |
+-----------------------------+
|                          18 |
+-----------------------------+

Let’s swap the two parameters and see what happens:

SELECT PERIOD_DIFF(202302, 202408);

Output:

+-----------------------------+
| PERIOD_DIFF(202302, 202408) |
+-----------------------------+
|                         -18 |
+-----------------------------+

Example 2 - Two digit year

For two-digit years, MariaDB handles them as follows:

  • Values ​​from 00 to 69 will be converted 2000 to 2069
  • Values ​​from 70 to 99 will be converted 1970 to 1999
SELECT
    PERIOD_DIFF(6901, 6812),
    PERIOD_DIFF(7001, 6912);

Output:

+-------------------------+-------------------------+
| PERIOD_DIFF(6901, 6812) | PERIOD_DIFF(7001, 6912) |
+-------------------------+-------------------------+
|                       1 |                   -1199 |
+-------------------------+-------------------------+

Conclusion

In MariaDB, PERIOD_DIFF() is a built-in function that returns the number of months between two periods specified by year and month.