Date Arithmetic and Intervals in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this Date and Time Types in PostgreSQL
Builds toward generate_series() for Sequences and Date Spines
What is Date Arithmetic in SQL?
INTERVAL is how you shift a date or timestamp by a span of time — forward or backward.
Date arithmetic shows up constantly in analytical SQL. Filtering for events in the last 30 days, calculating how many days elapsed between a signup and a first purchase, shifting a period boundary forward by one month — all of these require adding or subtracting a duration from a point in time. The INTERVAL type represents those durations.
How do you add days or months to a date in PostgreSQL?
An INTERVAL literal is a quoted string with a unit: '30 days', '3 months', '1 year 6 months', '2 hours'. Adding or subtracting it from a date or timestamp shifts that point in time by the specified duration:
SELECT CURRENT_DATE - INTERVAL '30 days' AS thirty_days_ago, CURRENT_DATE + INTERVAL '3 months' AS three_months_ahead
Adding an INTERVAL to a DATE returns a TIMESTAMP, not a DATE. An interval can carry hours and minutes, so the result has to be a type that can hold them. When you want to shift a date and keep a DATE, add a plain integer instead — CURRENT_DATE - 30 moves back thirty whole days and stays a DATE. Adding an INTERVAL to a TIMESTAMP or TIMESTAMPTZ does return the same type back.
What do you get when you subtract two dates in PostgreSQL?
Subtracting two dates from each other works differently depending on the type:
SELECT '2024-03-15'::date - '2024-01-01'::date AS days_between, '2024-03-15 12:00'::timestamp - '2024-01-01 09:00'::timestamp AS interval_between
DATE - DATE returns an integer: the number of days between the two dates. TIMESTAMP - TIMESTAMP returns an INTERVAL. These are different types — if you need the date difference as an INTERVAL, cast it explicitly.
When you subtract two TIMESTAMPTZ values, PostgreSQL converts both to UTC before computing the difference. The result reflects the true elapsed time regardless of time zones. Subtracting two TIMESTAMP values computes a difference in local clock time with no time zone adjustment, which can produce wrong durations when the timestamps were recorded in different time zones.
Is INTERVAL '1 month' the same as INTERVAL '30 days'?
One thing worth knowing: INTERVAL '1 month' and INTERVAL '30 days' are not the same thing. A month is a variable-length unit — 28, 29, 30, or 31 days depending on which month and year it falls in. PostgreSQL stores months and days separately inside an INTERVAL and applies them separately.
What does adding one month to January 31st return?
The one thing that trips people up: month-end clamping.
Adding INTERVAL '1 month' to January 31st produces February 28th (or 29th in a leap year). PostgreSQL clamps the result to the last valid day of the target month. No error, no warning. If you build a date series by repeatedly adding INTERVAL '1 month' to a date anchored at the 31st, some months clamp and some don't, producing inconsistent period boundaries. For calendar-aligned period generation, truncation with DATE_TRUNC is more reliable than addition from a month-end anchor.
You run '2024-01-31'::date + INTERVAL '1 month'. What does PostgreSQL return?
Practice Date Arithmetic in SQL
Brightlane's HR team schedules a probation review 30 days after each hire date.
Write a query to return the review date for a hire on '2024-03-15', computed as the hire date plus 30 days.
Output:
- A single row with one column,
review_date, containing the projected review date.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution10 Date Arithmetic practice problems
Write a query to return the review date for a hire on '2024-03-15', computed as the hire date plus 30 days.
Write a query to return the date produced by subtracting 1 month from '2024-06-15'.
Write a query to return the number of days between the start date '2024-01-01' and the end date '2024-03-15'.
Write a query to return the new start time produced by adding 3 hours to the timestamp '2024-01-15 09:00:00'.
Write a query to return the duration between the placement timestamp '2024-01-01 09:00:00' and the shipment timestamp '2024-03-15 12:00:00'.
Write a query to return the expiry date for a contract that begins on '2024-01-15', computed as the start date plus 1 year and 6 months.
Write a query to return the number of days between '2024-01-01' and '2024-12-31'.
Write a query to return the date exactly 1 calendar month after '2024-01-31'.
Write a query to return both the date produced by adding 1 calendar month to '2024-01-31' and the date produced by adding 30 days to that same starting date, in a single row.
Write a query to return the true elapsed time between the two timezone-aware timestamps '2024-03-15 09:00:00+00' and '2024-03-15 18:00:00+05:30'.
Start learning to practice all 10 Date Arithmetic problems, with instant grading and mastery tracking.
Deeper guides on Date Arithmetic
- what to use instead of DATEDIFF
Subtract the dates, and read why PostgreSQL answers DATEDIFF with a missing column. Hours, months and years too.
- adding days and months to a date
Add an integer or an INTERVAL, and see which one leaves you holding a timestamp.
- what now() returns inside a transaction
now() and current_date, why now() stands still for a whole transaction, and the filter that drops today’s rows.
Common questions about Date Arithmetic
Why does subtracting two dates return a number but subtracting two timestamps return an interval?
The result type follows the types you subtract. DATE minus DATE is an integer count of days. TIMESTAMP minus TIMESTAMP is an INTERVAL, because the gap can include hours, minutes and seconds that a whole number of days cannot express.
What does adding one month to January 31st return?
February 28th, or February 29th in a leap year. There is no February 31st, so PostgreSQL clamps the result to the last real day of the target month instead of rolling over into March.
Can I add a number to a date in PostgreSQL?
To a DATE, yes: adding an integer adds that many days. To a TIMESTAMP, no: timestamp plus integer is an error, because a timestamp has no single natural unit. Add an INTERVAL instead, which says exactly how much time you mean.
Is INTERVAL '1 day' the same as INTERVAL '24 hours'?
Not across a daylight saving change. '1 day' moves to the same clock time on the next calendar day. '24 hours' adds exactly twenty-four hours of elapsed time, so on the night the clocks spring forward it lands an hour later on the clock than '1 day' does.
