Brightlane's data engineering team is documenting how a calendar-month duration and a fixed-day duration behave differently when applied to a month-end date.
Write a query to return both the date produced by adding 1 calendar month to '2024-01-31' and the date produced by adding 30 days to that same starting date, in a single row.
Output:
- A single row with columns
one_month_later(the date one calendar month after'2024-01-31') andthirty_days_later(the date thirty days after'2024-01-31').2024is a leap 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 one_month_later,
'2024-01-31'::date + INTERVAL '30 days' AS thirty_days_laterThe shape
The two columns in the result show how a calendar-month duration and a fixed-day duration diverge when applied to a month-end date. Adding INTERVAL '1 month' to January 31, 2024 clamps to February 29, the last valid day of February. Adding INTERVAL '30 days' advances thirty literal days and lands on March 1, because thirty days from January 31 crosses into March in a year where February has 29 days.
Clause by clause
SELECT '2024-01-31'::date + INTERVAL '1 month' AS one_month_later, '2024-01-31'::date + INTERVAL '30 days' AS thirty_days_laterevaluates two independent date arithmetic expressions in one row. Both start from the same anchor,'2024-01-31'::date. The first applies a calendar-month interval, which preserves day-of-month when possible and clamps when not, producing February 29. The second applies a fixed thirty-day interval, which advances the date by exactly thirty days regardless of month boundaries, producing March 1.
Why the two intervals diverge
INTERVAL stores months and days as separate components, because a month is a variable-length unit. '1 month' is one month, applied as a month-component shift. '30 days' is thirty days, applied as a day-component shift. They have no fixed conversion. In February of a non-leap year the two would also diverge but in the opposite direction. The interval type is precise on purpose: it preserves the unit you wrote so the calendar logic stays consistent with the domain.
The trap
A learner who reads "thirty days is roughly a month" and treats the two intervals as interchangeable will produce inconsistent period boundaries the moment the anchor is a month-end date. The two are never equivalent for any anchor near the end of a month, and they diverge by between one and three days depending on the month. When the schedule needs to align to calendar months, use INTERVAL '1 month'. When the schedule needs a fixed day count, use INTERVAL '30 days'. The unit is part of the meaning of the duration.
You practiced contrasting INTERVAL '1 month' against INTERVAL '30 days' from the same anchor — calendar-month durations clamp to month boundaries; day-count durations advance a fixed number of days regardless of month length.
