In PostgreSQL, why can adding interval '1 day' to a timestamptz differ from adding '24 hours', and what does '1 month' do on January 31?
answer
- three separate fields inside
- months, then days, then time
- local time of day kept
- clamped to the month's end
- 30-day months when comparing
basics
~20 sA PostgreSQL interval keeps months, days and microseconds apart. Adding '1 day' keeps the local time of day across a DST change while '24 hours' adds elapsed time, and January 31 plus '1 month' clamps to February's last day.
solid answer
~40 sAn `interval` is stored as three integers — months, days and microseconds — because a month has 28 to 31 days and a day can have 23 or 25 hours. Adding one to a `timestamptz` applies them in that order: months move the calendar date, clamping to the last day when the target month is shorter, so 2026-01-31 plus `'1 month'` is 2026-02-28; days move the date while keeping the local wall-clock time in the session `TimeZone`; microseconds are added literally. So across the spring-forward night in `Europe/Paris`, noon plus `'1 day'` is noon the next day, 23 elapsed hours later, while noon plus `'24 hours'` lands at 13:00. Subtracting two `timestamptz` values returns 24-hour days plus a time part, never months; `age()` gives a calendar-style answer instead.
code
sql · 10 linesSET TimeZone = 'Europe/Paris'; -- clocks jump 02:00 -> 03:00 on 2026-03-29
SELECT timestamptz '2026-03-28 12:00' + interval '1 day'; -- 2026-03-29 12:00:00+02
SELECT timestamptz '2026-03-28 12:00' + interval '24 hours'; -- 2026-03-29 13:00:00+02
SELECT date '2026-01-31' + interval '1 month'; -- 2026-02-28 00:00:00
SELECT date '2026-01-31' + interval '1 month' + interval '1 month'; -- 2026-03-28 00:00:00
SELECT date '2026-01-31' + interval '2 months'; -- 2026-03-31 00:00:00
SELECT interval '1 mon' = interval '30 days'; -- truego deeper
Recall that interval has separate month, day and time parts, so '1 day' and '24 hours' are not interchangeable.
Walk through the order of application, month-end clamping, and why the session TimeZone decides what a day means for timestamptz arithmetic.
Show the bugs this causes: recurring schedules drifting across DST, month-stepping billing landing on the 28th, and durations computed with age() instead of subtraction.
Decide which business quantities are calendar periods and which are elapsed durations, and make that distinction explicit in the schema and the APIs that feed it.
## Why interval has three fields A PostgreSQL **`interval`** (16 bytes) is not a number of seconds. It is stored as three separate integers: - **months** — because months have 28 to 31 days; - **days** — because a civil day has 23 or 25 hours on a daylight-saving transition; - **microseconds** — the exact time part. Input is normalised into those fields: `'2 years 15 months'` becomes 3 years 3 months, while hours, minutes and seconds are folded into the time part. Because the fields stay apart, `'1 day'` and `'24 hours'` are different values that only *sometimes* produce the same result. ## The order in which an interval is applied Adding an interval to a `timestamp` or `timestamptz` proceeds field by field: 1. **Months** move the calendar date, keeping the day of month unless the target month is shorter, in which case the **last day** of that month is used. 2. **Days** move the calendar date by that many days. In both steps the **local time of day stays the same**. 3. **Microseconds** are then added literally, as elapsed time. For a `timestamptz`, *local* means local in the session's `TimeZone`. ## Daylight saving: '1 day' vs '24 hours' In a zone with daylight saving, the days step and the time step diverge on transition days. In `Europe/Paris`, clocks jump from 02:00 to 03:00 on 2026-03-29: - `timestamptz '2026-03-28 12:00' + interval '1 day'` gives 12:00 on the 29th — only 23 hours of real time; - `+ interval '24 hours'` gives 13:00 on the 29th — exactly 24 hours later. With the session `TimeZone` set to `UTC`, there are no transitions and the two agree. Plain `timestamp` has no zone, so it never sees a transition either. When the session zone is not the zone you mean, `date_add(ts, interval, 'Europe/Paris')` applies the days and months steps in the named zone. ## Month arithmetic is clamped and not reversible - `date '2026-01-31' + interval '1 month'` is `2026-02-28 00:00:00` (a date plus an interval yields a `timestamp`); - March 31 plus 1 month is April 30, but March 31 plus 2 months is May 31; - adding one month twice is therefore not the same as adding two months: January 31 becomes February 28 and then March 28; - subtracting the month back does not return you to January 31. That is why billing or schedule code that steps month by month from a previous result drifts toward the 28th; step from the original anchor instead. ## Measuring a gap | Expression | Result for a span across a DST change | Meaning | |---|---|---| | `b - a` | `121 days 23:00:00` | 24-hour days plus time, real elapsed time | | `age(b, a)` | `4 mons` | calendar fields, field-by-field | | `EXTRACT(EPOCH FROM b) - EXTRACT(EPOCH FROM a)` | seconds | exact elapsed seconds | The example is the manual's: noon on March 1 to noon on July 1 in `US/Eastern`, which loses an hour in March. A flight's block time is `arrives_at - departs_at` on two `timestamptz` values, which is real elapsed time even when the origin and destination zones differ. ## Comparison and normalisation - Comparing intervals treats a month as **30 days** and a day as **24 hours**, so `interval '1 mon' = interval '30 days'` is true even though adding them to January 31 gives different dates. - `justify_days` rewrites 30-day runs as months, `justify_hours` rewrites 24-hour runs as days, and `justify_interval` does both; use them for display, knowing that they change arithmetic results. - Since PostgreSQL 17, `interval` also accepts `infinity` and `-infinity`. ## Generating schedules The same rules drive set-returning functions. `generate_series(start, stop, interval '1 day', 'Europe/Paris')` on `timestamptz` values computes each step's day and daylight-saving adjustment in the named zone, or in the session `TimeZone` when the fourth argument is omitted. Other operations follow the field model too: - multiplying scales each field: `interval '1 day' * 1.5` is `1 day 12:00:00`; - `EXTRACT(EPOCH FROM interval '1 mon')` uses the same 30-day assumption, so it is not a real month's length; - an interval with a negative field, such as `'1 mon -1 day'`, is legal and is applied field by field like any other. ## For the flight schedule A daily 08:00 local departure is generated by adding `'1 day'` in the origin airport's zone, so it stays at 08:00 across the clock change. A minimum connection time is `'45 minutes'` added to an arrival instant, which must be elapsed time. Choosing the field is choosing the meaning.
- The session TimeZone is UTC; how do you add one local day in the departure airport's zone?Use `date_add(departs_at, interval '1 day', 'Europe/Paris')`, whose third argument names the zone used for the day and month steps. The equivalent by hand is `((departs_at AT TIME ZONE 'Europe/Paris') + interval '1 day') AT TIME ZONE 'Europe/Paris'`. In a UTC session a plain `+ interval '1 day'` is always 24 hours, which drifts by an hour across the airport's clock change.
- In PostgreSQL, what does subtracting two timestamptz values return, and when would you use age() instead?`b - a` returns an interval of 24-hour days plus a time part, measuring real elapsed time; it never produces months. `age(b, a)` subtracts field by field and returns years, months and days, which suits human-facing answers such as tenure or an age. For exact seconds, subtract the two `EXTRACT(EPOCH FROM ...)` values.
- An interval is stored internally as a single number of seconds.
- interval '1 day' always equals interval '24 hours' when added to a timestamptz.
- January 31 plus one month rolls over into early March.
- Adding one month twice always equals adding two months.
- Subtracting two timestamptz values returns months and days.
Assumes PostgreSQL 18; interval has accepted infinity and -infinity only since 17, and the arithmetic rules shown are unchanged in 18.