Brightlane's subscription platform schedules monthly renewal dates for plans activated at the end of January. A plan activated on '2024-01-31' is scheduled to renew approximately one calendar month after each previous renewal.
Write a query to return all renewal dates produced by generate_series with a start of '2024-01-31', an end of '2024-04-30', and a step of '1 month'. Each result should be cast back to DATE.
Assumptions:
- The four result dates are exactly
'2024-01-31','2024-02-29','2024-03-29', and'2024-04-29'. The function emits these four dates and stops because the next candidate would exceed the end value. 2024is a leap year, which is why the second value is February29rather than February28.- After the first month-end value falls back to
29, subsequent steps preserve the29day-of-month rather than returning to31.
Output:
- Four rows, with one column,
renewal_date, typed as a date and holding'2024-01-31','2024-02-29','2024-03-29','2024-04-29'in that order.
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
GENERATE_SERIES('2024-01-31'::date, '2024-04-30'::date, INTERVAL '1 month')::date AS renewal_dateThe shape
generate_series adds a one-month interval to the start date and to each subsequent value. When the start is January 31 and one month forward would land on February 31 (a date that does not exist), PostgreSQL clamps to the last valid day of the target month. The clamped value, not the original start, anchors the next step, so every subsequent value sits on the 29th rather than the 31st.
Clause by clause
generate_series('2024-01-31'::date, '2024-04-30'::date, interval '1 month')emits the start date and walks forward one month at a time. The function continues emitting values as long as the next candidate does not exceed the end of'2024-04-30'.- The first value emitted is
'2024-01-31'itself. Adding one month produces'2024-02-29'because 2024 is a leap year and February has 29 days; without leap day, the clamp would land on'2024-02-28'. - From
'2024-02-29', adding one month produces'2024-03-29'. The interval arithmetic preserves the day-of-month of its input, so once February clamped the value to the 29th, every subsequent step stays on the 29th. - The fourth value,
'2024-04-29', sits one day before the end of'2024-04-30', so it is emitted. The fifth candidate would be'2024-05-29', which exceeds the end, and the function stops. - The trailing
::datecast converts each timestamp back to a calendar date, andAS renewal_datelabels the column for the subscription report.
The trap
The clamp-and-anchor behaviour is permanent. Once February drags the day-of-month down from 31 to 29, the function will never return to 31 even in months that have one. A subscription that activated on January 31 will renew on the 29th of every month going forward, not on the last day of each month. Two callers can read the same code and disagree on which is correct: month-end logic (always renew on the last day available) requires date_trunc('month', dt) + interval '1 month' - interval '1 day', while same-day-each-month logic accepts the clamp. Pick the behaviour the business needs and write it explicitly rather than inheriting whichever the interval arithmetic happens to produce.
You practiced generate_series with a monthly step starting at month-end — PostgreSQL's interval-add clamps to the last valid day of each target month, and subsequent steps anchor on the clamped value rather than the original start.
