Date Arithmetic and Intervals in SQL
PostgreSQL supports arithmetic between date/time values and a dedicated duration type, `INTERVAL`, which represents a span of time rather than a point in it. The result type of a date/time arithmetic expression depends on the types of both operands.
Before this Date and Time Types in PostgreSQL
Builds toward generate_series() for Sequences and Date Spines
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.
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:
Adding an INTERVAL to a DATE returns a DATE. Adding it to a TIMESTAMP or TIMESTAMPTZ returns the same type back.
Subtracting two dates from each other works differently depending on the type:
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.
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.
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?
10 Date Arithmetic and Intervals 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'`.
These problems are part of the Date Arithmetic and Intervals lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.