skip to content

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?

level: middleimportance: should knowfreq 34%

answer

  1. three separate fields inside
  2. months, then days, then time
  3. local time of day kept
  4. clamped to the month's end
  5. 30-day months when comparing

basics

~20 s

A 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 s

An `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 lines
sql
SET 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';                      -- true

go deeper

for a junior

Recall that interval has separate month, day and time parts, so '1 day' and '24 hours' are not interchangeable.

for a middle

Walk through the order of application, month-end clamping, and why the session TimeZone decides what a day means for timestamptz arithmetic.

for a senior

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.

for a principal

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.