2026-10-17 · 8 min read
- postgresql
- sql
- dates
A PostgreSQL interval can hold months, days, and clock time at once. Addition applies the months, then the days, then the time. If the day of the month does not exist after the month step, PostgreSQL clamps it to the last valid day. That is calendar arithmetic, not a rule that the original day was the end of the month.
date '2026-01-31' + interval '1 month' is 2026-02-28. date '2024-01-31' + interval '1 month' is 2024-02-29. The next month step does not restore the 31st: date '2026-02-28' + interval '1 month' is 2026-03-28. Adding an interval to a date returns timestamp without time zone, so a later comparison should account for the type.
Prefer one addition when the month count is the contract
date '2026-01-31' + interval '2 months' is 2026-03-31. March has a 31st, so a single addition does not clamp. The expression date '2026-01-31' + interval '1 month' + interval '1 month' clamps at February and finishes on 2026-03-28. A loop that adds one month per iteration is the two-step expression. A scheduled job that adds the whole span once is the other.
Multiplying does not turn months into days. interval '1 month' * 2 is an interval of two months. Added once, it behaves like interval '2 months'. If the product is applied inside a loop, each iteration can clamp.
- Store the start date beside the interval text.
- Say whether production adds the interval once.
- Treat a loop of one-month steps as a different formula.
- Record the leap-year result next to the common-year result.
Leave justify_days out of calendar addition
justify_days rewrites an interval so that 30 days become one month. justify_days(interval '40 days') is 1 mon 10 days. That is not a 40-day duration anymore. date '2026-01-31' + interval '40 days' is 2026-03-12. date '2026-01-31' + justify_days(interval '40 days') clamps through February and lands on 2026-03-10. The two results are two days apart, and a leap year moves the clamp to the 29th, so the gap changes again.
justify_hours rewrites 24 hours as a day, and justify_interval applies both rewrites. Use them when you mean to display a normalized interval. Do not use them to prepare a value for date arithmetic unless a 30-day month is an explicit contract. Days and months can stay in one interval value. Knowing they are applied in that order is the whole fix.
Separate this clamp from time zone display
A result that moved from the 31st to the 28th is this clamp. A result that moved by an hour, or that prints differently in two sessions, is the session time zone acting on timestamptz. Adding a day to timestamptz follows the calendar in the session zone. Fix the interval expression first when the day of the month changed. Read the time zone operator when the civil time changed and the date did not.
The SQL formatter pretty-prints the statement and does not execute it. Run the one-step expression and the stepped expression in PostgreSQL and diff the outputs. The timestamp converter accepts Unix seconds, so extract the epoch only after you have a real instant, and do not paste a date string or a millisecond count. Use the JSON formatter when the API returns the timestamp as text. The migration note should show the start date, the interval, and both results.