Adding a Month to January 31

Concepts covered: Date Math, Date Truncation, Join Cardinality

This one arrives as "when is the next billing date?", "compare to the same day last year" or "retention 1 month after signup". A month has no fixed length, so + 1 month is a rule the engine applies to the calendar fields rather than a quantity of time. Nearly every engine applies the same rule for a single step: add to the month and keep the day, clipping it to the month's last day when it overflows.

SELECT sub_id,
       start_date,
       CAST(start_date + INTERVAL '1' MONTH AS DATE) AS plus_1,
       CAST(start_date + INTERVAL '1' MONTH
                       + INTERVAL '1' MONTH AS DATE) AS chained_2,
       CAST(start_date + INTERVAL '2' MONTH AS DATE) AS plus_2
FROM subscriptions;

Subscription 1 starts on 2024-01-31 and its next month is 2024-02-29, the clip. Adding a second month to that result gives 2024-03-29, while adding 2 months to the start gives 2024-03-31: the days lost in February never come back when steps are chained. Subscription 3 shows a leap day moving to 2024-03-29 and staying on the 29th, and subscription 4 shows a 31st clipping to 2024-04-30 and then drifting to 2024-05-30.

The clip also makes month math irreversible. Taking 2024-01-31 forward 1 month and back 1 month returns 2024-01-29, so a filter written as renewal_date - INTERVAL '1 month' = start_date misses every start that was clipped. And "1 month" and "30 days" part ways at once: 2024-01-31 plus 30 days is 2024-03-01, plus 1 month is 2024-02-29, and a trial sold as a month but coded as 30 days ends on a different date for most starts in a month that is not 30 days long. The exceptions are starts where the clip happens to land on the same day, such as 2024-01-30, which reaches 2024-02-29 either way.

Anchor every schedule to its start

A schedule computed as anchor plus n months never drifts, because each date is a single clip away from the start rather than an accumulation of clips. That reproduces the behaviour Stripe documents for monthly subscriptions anchored on the 31st: February 29 in a leap year, then March 31, April 30. The rule the interviewer probes is the anchor that is a month end without being a 31st, such as April 30. Same-day-number gives May 30, last-day-stays-last-day gives May 31, and only the business can say which one it bills.

-- 12 monthly due dates per subscription, each from the anchor
SELECT s.sub_id,
       n.k AS cycle,
       CASE
         WHEN s.start_date = CAST(DATE_TRUNC('month', s.start_date)
                                  + INTERVAL '1 month' - INTERVAL '1 day' AS DATE)
           -- month-end anchor: stay on the last day of every month
           THEN CAST(DATE_TRUNC('month', s.start_date)
                     + (n.k + 1) * INTERVAL '1 month' - INTERVAL '1 day' AS DATE)
         ELSE CAST(s.start_date + n.k * INTERVAL '1 month' AS DATE)
       END AS due_date
FROM subscriptions s
CROSS JOIN generate_series(1, 12) AS n(k);

February 29 and year math

Adding a year is the same rule on the year field, so 2024-02-29 plus 1 year is 2025-02-28 in Postgres and BigQuery, and in Snowflake too. Where this costs money is the same-day-last-year join.

Joined on ly.day = ty.day - INTERVAL '1 year', the 2023-02-28 row matches 2 rows this year, so last year's February counts that day twice and the growth figure comes out low. Written the other way, ly.day + INTERVAL '1 year' = ty.day, February 29 matches nothing. The business has to say whether February 29 compares to February 28 or to nothing. Retail usually sidesteps it by comparing to 364 days back, which keeps a Saturday against a Saturday at the price of the calendar dates drifting apart year after year.

Anniversaries follow the same clip. Someone born on 2004-02-29 turns 21 on 2025-02-28 under the engine's rule, while legal rules differ on whether that birthday falls on February 28 or March 1. Any age or tenure check near the boundary is a policy question to ask. Completed years come from comparing month and day against the anniversary, never from subtracting 2 year numbers.

About this lesson

This section is part of the Date Arithmetic: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.