Brightlane's subscription platform schedules monthly renewals. A subscriber's most recent renewal was on '2024-01-31', and the team needs the date of the next renewal one calendar month later.
Write a query to return the date exactly 1 calendar month after '2024-01-31'.
Output:
- A single row with one column,
next_renewal, containing the renewal date.2024is a leap year, so the result is the last valid day of February in that year.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
'2024-01-31'::date + INTERVAL '1 month' AS next_renewalThe shape
Adding INTERVAL '1 month' to January 31, 2024 returns February 29, 2024. PostgreSQL preserves the day-of-month when the target month has it, and clamps to the last valid day when it does not. February has at most 29 days in 2024, so the result clamps to the 29th.
Clause by clause
SELECT '2024-01-31'::date + INTERVAL '1 month' AS next_renewalbuilds the renewal date. The::datecast types the literal as a calendar date. Adding a calendar-monthINTERVALadvances the month component by one. PostgreSQL then resolves the day-of-month: January's 31 maps to February's last valid day, which is the 29th in this leap year. The aliasAS next_renewalnames the output for the subscription scheduler.
Why the result clamps and does not error
PostgreSQL treats month arithmetic as best-effort, not exact. There is no February 31, so an exact match is impossible. The rule the database applies is to round down to the last day that exists in the target month. No error fires, no NULL is returned, and the query produces a usable date. The clamping behavior is deterministic and documented, but it is silent.
The trap
A monthly renewal schedule built by repeatedly adding INTERVAL '1 month' to the original sign-up date can drift in ways that surprise the business. A subscriber who signed up on January 31 renews on February 29 in 2024, then on March 29 in the next step rather than returning to the 31st, because each step adds one month to whatever the previous result was, not to the original anchor. For consistent month-end renewals, anchor each step at the original date or use a date-truncation construction instead of chaining month additions.
You practiced date + INTERVAL '1 month' against a month-end starting date — when the target month has no matching day-of-month, PostgreSQL clamps the result to the last valid day of that month.
