PERIOD_ADD in MariaDB: Adding and Subtracting Months from Periods

Purpose

• It adds a specified number of months to a given period and returns the resulting period as a string in the same format.
• PERIOD_ADD is a date-time function specifically designed for working with periods represented as strings in the format YYMM (two-digit year and month) or YYYYMM (four-digit year and month).

Syntax

PERIOD_ADD(period, interval)

• interval: The number of months to add. It can be a positive or negative integer.
• period: The starting period you want to add months to. It must be a string in the format YYMM or YYYYMM.

Example

SELECT PERIOD_ADD('202305', 3) AS new_period;

• The result will be 202308 (August 2023).
• This query adds 3 months to the period 202305 (May 2023).

Key Points

• PERIOD_ADD is a convenient way to handle calculations involving periods without needing to convert them to full date/time values.
• If the resulting period exceeds the maximum value for a month (e.g., adding 11 months to December), it will wrap around to the next year (e.g., December + 11 months becomes November of the following year).
• Two-digit years are interpreted according to the following rules:
  • Values from 00 to 69 are considered to be in the range 2000 to 2069.
  • Values from 70 to 99 are considered to be in the range 1970 to 1999.

• For more complex date/time operations, consider using MariaDB's built-in date/time data types and functions.
• While PERIOD_ADD is useful for basic period manipulation, MariaDB also offers the DATE_ADD function for more comprehensive date-time calculations involving days, months, years, etc.

---

Adding Months to a Period (Positive Interval)

SELECT PERIOD_ADD('202401', 6) AS six_months_later;  -- Output: 202407 (July 2024)
SELECT PERIOD_ADD('199812', 12) AS one_year_later;   -- Output: 199912 (December 1999)

Subtracting Months from a Period (Negative Interval)

SELECT PERIOD_ADD('202308', -3) AS three_months_ago;  -- Output: 202305 (May 2023)
SELECT PERIOD_ADD('200002', -18) AS year_and_a_half_ago; -- Output: 199808 (August 1998)

Handling Period Wrap-Around

SELECT PERIOD_ADD('202312', 11) AS next_november;  -- Output: 202411 (November 2024)
SELECT PERIOD_ADD('202402', -13) AS previous_january; -- Output: 202301 (January 2023)

Using PERIOD_ADD with a Variable

DECLARE starting_period VARCHAR(6) DEFAULT '202203';
SET starting_period = '202009';  -- Change the starting period dynamically

SELECT PERIOD_ADD(starting_period, 24) AS two_years_later;

SELECT YEAR(PERIOD_ADD(MAX(sale_date), -12)) AS previous_year_sales;

---

Using DATE_ADD

• To achieve the same effect as PERIOD_ADD, you can convert the period string to a date using STR_TO_DATE, add the desired number of months using DATE_ADD, and then format the result back to a period string using DATE_FORMAT.
• DATE_ADD is a more versatile function that works with various date and time formats, including full dates (YYYY-MM-DD), timestamps (YYYY-MM-DD HH:MM:SS), and periods (YYMM or YYYYMM).

SELECT DATE_FORMAT(DATE_ADD(STR_TO_DATE('202305', '%Y%m'), INTERVAL 3 MONTH), '%Y%m') AS new_period;

This approach offers more flexibility if you need to perform additional date/time manipulations before or after the period addition.

Using Derived Tables (for calculations within queries)

• You can create a derived table that calculates the desired period addition using DATE_ADD and then reference it in your main query.

WITH added_months AS (
  SELECT STR_TO_DATE('202402', '%Y%m') AS starting_period
)
SELECT DATE_FORMAT(DATE_ADD(starting_period, INTERVAL 6 MONTH), '%Y%m') AS six_months_later
FROM added_months;

This method can be useful when you need to perform the period addition multiple times within a larger query.

• If you require more complex date/time calculations or want to integrate period addition with other operations within a query, DATE_ADD or derived tables might be more suitable.
• If you're dealing strictly with period strings and only need basic addition/subtraction of months, PERIOD_ADD is a convenient choice for its simplicity.