How the PERIOD_ADD() function works in Mariadb?

The PERIOD_ADD() function is a built-in function in Mariadb that returns a period that is a number of months away from a given period.

Posted on

The PERIOD_ADD() function is a built-in function in Mariadb that 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.

Syntax

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

PERIOD_ADD(period, months)

Where period is a four-digit number that represents a period, and months is an integer value that represents the number of months to add or subtract. If period or months is NULL, the function returns NULL.

Examples

Example 1: Adding months to a period

The following example shows how to use the PERIOD_ADD() function to add months to a period:

SELECT PERIOD_ADD(202101, 3) AS NewPeriod;

The output is:

+-----------+
| NewPeriod |
+-----------+
|    202104 |
+-----------+

The function returns 202104, which is the period of April 2021. The function adds 3 months to the period of January 2021.

Example 2: Subtracting months from a period

The following example shows how to use the PERIOD_ADD() function to subtract months from a period:

SELECT PERIOD_ADD(202101, -3) AS NewPeriod;

The output is:

+-----------+
| NewPeriod |
+-----------+
|    202010 |
+-----------+

The function returns 202010, which is the period of October 2020. The function subtracts 3 months from the period of January 2021.

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

  • PERIOD_DIFF(): This function 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.
  • DATE_ADD(): This function returns a date or datetime that is a specified interval away from a given date or datetime. The function is also known as ADDDATE(). The function allows you to specify the interval type and value, such as day, week, month, year, etc. The function is useful for adding or subtracting intervals from a date or datetime.
  • DATE_SUB(): This function returns a date or datetime that is a specified interval before a given date or datetime. The function is also known as SUBDATE(). The function allows you to specify the interval type and value, such as day, week, month, year, etc. The function is useful for subtracting intervals from a date or datetime.

Conclusion

The PERIOD_ADD() function is a useful function in Mariadb that allows you to return 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. The function is useful for adding or subtracting months from a time period. You can also use other functions like PERIOD_DIFF(), DATE_ADD(), and DATE_SUB() to manipulate time periods in different ways. I hope this article helped you understand how the PERIOD_ADD() function works in Mariadb.