SQL / TEXT, NUMERIC, AND DATE FUNCTIONS
Adding and subtracting intervals of time
Add and subtract time with interval arithmetic in SQL, predicting month-end clamping, DST shifts, and result types instead of guessing at 30-day months.
What you will learn
- Choose calendar units (month, year) or fixed units (day, hour) deliberately
- Predict month-end clamping: 2024-01-31 + 1 month gives 2024-02-29
- Build recurring dates as anchor + n * interval so the schedule never drifts
- Write windows as col >= boundary - INTERVAL '30 days' to keep the column indexable
Understanding Adding and subtracting intervals of time
An interval is not a number of seconds. PostgreSQL stores one in three independent fields, months, days, and microseconds, and each field is applied differently when it meets a date: the month field shifts the month number and then clamps the day to the last valid day of the target month, the day field walks the calendar, and the microsecond field adds clock time. That is why INTERVAL '1 month' has no fixed length until it is applied: on 2024-01-31 it lands on 2024-02-29, on 2024-03-31 it lands on 2024-04-30, and on 2024-04-15 it moves exactly 30 days.
Clamping throws information away, so month arithmetic does not undo itself and is not associative. DATE '2024-01-31' + INTERVAL '1 month' - INTERVAL '1 month' is 2024-01-29, because the intermediate value only knows that it is the 29th; the original day 31 is gone. The working rule is to keep one anchor date and compute anchor plus n months in a single step, rather than feeding each result back in as the next input, which quietly walks a monthly schedule off the 31st and onto the 28th or 29th for good.
Where the arithmetic sits matters as much as the unit. Compare a bare column against a computed constant, as in created_at >= boundary - INTERVAL '30 days', so an index on created_at remains usable. Result types shift too: in PostgreSQL date + interval promotes to timestamp, while date + 30, a plain integer, stays a date. On timestamptz values the day and month fields follow the local calendar across a DST change, while hour, minute, and second fields are pure elapsed time.
SELECT DATE '2024-01-31' + INTERVAL '1 month' AS add_month,
DATE '2024-01-31' + INTERVAL '30 days' AS add_30_days,
DATE '2024-01-31' + INTERVAL '1 month' - INTERVAL '1 month' AS round_trip;An interval is a calendar instruction applied to a specific date, not a fixed duration, so months clamp at month end and adding then subtracting a month does not return the original day.
Worked examples
Rolling window without touching the column
Subtracting the interval from the boundary constant keeps the filtered column bare.
WITH events(id, occurred_at) AS (
VALUES (1, TIMESTAMP '2024-05-01 09:00'),
(2, TIMESTAMP '2024-05-20 09:00'),
(3, TIMESTAMP '2024-06-02 09:00')
)
SELECT id, occurred_at
FROM events
WHERE occurred_at >= TIMESTAMP '2024-06-03 12:00' - INTERVAL '30 days'
ORDER BY id;Example explained
Line 1The boundary is computed once from the constant, so occurred_at is compared unwrapped and an index on it stays usable.
Line 2INTERVAL '30 days' subtracts thirty calendar days and lands on 2024-05-04 12:00, not on the 3rd of the previous month.
Line 3Row 1 at 2024-05-01 09:00 falls before that boundary and drops out; >= makes 2024-05-04 12:00 itself inclusive.
A monthly schedule that does not drift
Multiplying one interval by a cycle number off a fixed anchor avoids accumulated clamping.
SELECT n AS cycle,
DATE '2024-01-31' + (n * INTERVAL '1 month') AS due_from_anchor
FROM generate_series(0, 3) AS g(n);Example explained
Line 1generate_series(0, 3) supplies the cycle number, and integer times interval scales one month into n months.
Line 2Every row is computed from the same anchor, so cycle 2 returns to the 31st instead of being stuck on the 29th.
Line 3Cycle 1 clamps to 2024-02-29 and cycle 3 clamps to 2024-04-30, since neither February nor April has a 31st.
One day is not always 24 hours
On a timestamptz, a day interval follows the wall clock while an hour interval counts elapsed time.
SET TIME ZONE 'America/New_York';
SELECT TIMESTAMPTZ '2024-03-09 12:00' + INTERVAL '1 day' AS plus_1_day,
TIMESTAMPTZ '2024-03-09 12:00' + INTERVAL '24 hours' AS plus_24_hours;Example explained
Line 1SET TIME ZONE fixes the session zone, so the literal is read as 12:00 EST at offset -05.
Line 2INTERVAL '1 day' is applied to the local calendar, so the wall clock stays at 12:00 and only the offset changes to -04.
Line 3INTERVAL '24 hours' adds elapsed time across the hour that DST removed, landing an hour later at 13:00 local.
Important notes
PostgreSQL compares intervals by normalizing to 30-day months and 24-hour days, so INTERVAL '1 month' = INTERVAL '30 days' is true even though adding each to a date gives different results.
SQLite does not clamp; date('2024-01-31', '+1 month') overflows past the end of February and returns 2024-03-02, so month arithmetic that is correct in PostgreSQL or MySQL can differ there.
Common mistakes
Using INTERVAL '30 days' to mean one month: steps from 2024-01-31 land on 03-01, 03-31, 04-30, so a monthly report window slides against the calendar and rows near month end get counted in two periods or none.
Assuming the round trip is lossless and storing each computed renewal back into the column: once 2024-01-31 clamps to 2024-02-29, the next month gives 2024-03-29 and month-end customers stay on the 29th forever.
Writing WHERE created_at + INTERVAL '30 days' >= now() instead of moving the interval to the constant: the index on created_at cannot be used and the filter turns into a full scan.
Try it yourself
Change, predict, then run
In a browser SQL editor, produce four renewal dates for a signup of 2024-01-31 twice: once as 2024-01-31 + n * INTERVAL '1 month' for n from 0 to 3, and once by adding INTERVAL '1 month' to each previous result. Report the first cycle where the two disagree and by how much.
Open the SQL workspaceCheck your understanding
A subscription starts on 2024-01-31. Job A recomputes each renewal as signup + n months; Job B stores the previous renewal and adds one month to that. After several cycles, what happens?
- Job B collapses onto the 29th and stays there, because the February clamp discarded day 31
- Both jobs agree, because adding a month is associative
- Job A drifts a day later each cycle, because calendar months have different lengths
- Both jobs agree until a leap year, then Job A jumps ahead by one day
Show answer
The first step clamps 2024-01-31 to 2024-02-29, and that value no longer records day 31, so Job B continues 2024-03-29, 2024-04-29, and onward. Job A keeps the anchor and clamps only when the target month is genuinely shorter, giving 2024-03-31 and 2024-04-30. The associativity option is tempting because addition usually reorders freely, but clamping is a lossy step, not a fixed offset.