2026-10-17 · 7 min read · Rahul Chitturi
- postgresql
- sql
- dates
PostgreSQL adds an interval by applying months first, then days, then the clock time. If the resulting calendar day does not exist, the day is clamped to the last day of that month. date '2026-01-31' + interval '1 month' is 2026-02-28. date '2024-01-31' + interval '1 month' is 2024-02-29, because 2024 is a leap year. The clamp is not an end-of-month policy.
The day after a clamp is an ordinary day. date '2026-02-28' + interval '1 month' is 2026-03-28, not 2026-03-31. A value that fell back to the last day of February does not remember that it started as the 31st.
Add the interval once if the count must stay intact
date '2026-01-31' + interval '2 months' is 2026-03-31. The same start plus interval '1 month', and then plus interval '1 month' again, is 2026-02-28 and then 2026-03-28. Two steps clamp in the middle. One step asks March for day 31, and March has it.
interval '1 month' * 2 is still two months, added as one interval. It is not 60 days. interval '40 days' added to 2026-01-31 lands on 2026-03-12. justify_days rewrites the interval itself with a 30-day month, so justify_days(interval '40 days') is 1 mon 10 days. Added to 2026-01-31, that justified value clamps to 2026-02-28 and then moves 10 days, to 2026-03-10. The calendar addition and the justified interval are two days apart.
- Write the start date and the interval in the ticket.
- Say whether the interval is added once or in steps.
- Keep months and days as separate fields until you mean to mix them.
- Do not run justify_days unless a 30-day month is the contract.
Format the expression, then compare instants
Adding an interval to a date yields timestamp without time zone. Compare that result to a date column only after you decide which type the table stores. A timestamptz plus a day still follows the session time zone's calendar. An hour-sized surprise belongs with the time zone operator. A move from the 31st to the 28th belongs here.
Flashman's SQL formatter shows the expression and does not connect to a database, so it cannot tell you the clamped day. Run the expression in PostgreSQL. When you already have an instant as epoch seconds, paste those seconds into the timestamp converter. Do not paste 2026-02-28 and do not paste milliseconds. Diff the one-step result against the two-step result, and use the JSON formatter when either value is wrapped in an API field.