INTERVAL
INTERVAL is a SQL data type and literal syntax representing a span of time — such as '3 days' or '1 month' — used for date/timestamp arithmetic.
Overview
The INTERVAL type represents a duration rather than a point in time. It's used to add or subtract spans of time from dates and timestamps, and to express duration comparisons directly in SQL:
Copy code
SELECT
order_date,
order_date + INTERVAL '30 days' AS due_date,
CURRENT_DATE - INTERVAL '1 year' AS one_year_ago
FROM orders
WHERE order_date > CURRENT_DATE - INTERVAL '90 days';
Interval literals combine a number and a unit: INTERVAL '1' YEAR, INTERVAL '3' MONTH, INTERVAL '2' DAY, INTERVAL '90' MINUTE. Many engines also accept a compact string form like INTERVAL '1 year 2 months 3 days'.
Interval arithmetic
Adding an interval to a DATE or TIMESTAMP shifts it by that amount; subtracting two dates/timestamps produces an interval (a duration) rather than a plain number. This makes interval math self-documenting compared to hard-coding a number of seconds or days.
Copy code
SELECT delivered_at - shipped_at AS delivery_duration FROM shipments;
DuckDB specifics
DuckDB's INTERVAL type supports both single-unit literals (INTERVAL 5 DAY, INTERVAL '1' MONTH) and combined ones (INTERVAL '1 year 2 months 3 days'), plus arithmetic directly against DATE, TIMESTAMP, and TIMESTAMPTZ values. DuckDB also supports multiplying an interval by an integer (INTERVAL '1 day' * 7) and using date_trunc/date_diff alongside intervals for calendar-aware rollups. Because calendar units (months, years) have variable length, DuckDB — like most engines — normalizes month/year arithmetic to calendar rules rather than a fixed number of seconds, so date + INTERVAL '1 month' correctly lands on the same day next month (or the last valid day, for edge cases like January 31st).
Related terms
DATE_TRUNC(unit, value) rounds a date or timestamp down to the start of a specified unit — such as day, month, or year — commonly used to bucket time-series data.
EXTRACT →EXTRACT(field FROM value) pulls a single numeric component — such as year, month, or hour — out of a date, time, or timestamp value.
timestamps →Timestamps are a fundamental data type in databases and programming languages that represent a specific point in time, typically including both the date and…
GENERATE_SERIES →GENERATE_SERIES(start, stop, step) produces a sequence of numeric, date, or timestamp values, commonly used to build calendars or fill gaps in time-series data.
Role-playing dimension →A role-playing dimension is a single physical dimension table, most often a date dimension, that is joined into a fact table multiple times to represent different roles, such as order date, ship date, and delivery date.
FAQS
Subtracting two DATE or TIMESTAMP values produces an INTERVAL representing the elapsed duration between them. An INTERVAL literal like INTERVAL '7 days' is instead a fixed duration you construct explicitly, typically to add to or subtract from a date.
Yes. DuckDB supports interval arithmetic such as INTERVAL '1 day' * 7, which is useful for computing an offset that depends on a variable count.
