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.
Overview
GENERATE_SERIES produces an evenly spaced sequence of values between a start and stop bound, inclusive of both ends. It's most often used to generate a synthetic date/number spine — for example, a complete calendar of days — that you then LEFT JOIN real data onto, so that days or periods with zero activity still appear in the output.
Copy code
SELECT day
FROM generate_series(DATE '2026-01-01', DATE '2026-01-31', INTERVAL '1 day') AS t(day);
Copy code
-- Fill gaps: every day in January, even ones with no orders
SELECT d.day, COALESCE(SUM(o.revenue), 0) AS revenue
FROM generate_series(DATE '2026-01-01', DATE '2026-01-31', INTERVAL '1 day') AS d(day)
LEFT JOIN orders o ON DATE_TRUNC('day', o.order_date) = d.day
GROUP BY ALL
ORDER BY ALL;
DuckDB specifics
DuckDB's generate_series(start, stop, step) is dual-purpose: used in a FROM clause, it behaves as a table function producing one row per value (as in the examples above); used inside a SELECT list, it instead returns a single LIST value containing the whole sequence. DuckDB's generate_series is inclusive of the stop value, whereas its sibling function range(start, stop, step) behaves the same way but excludes the stop value — mirroring Python's range(). Both work over integers, dates, and timestamps (with an INTERVAL step for date/timestamp sequences):
Copy code
SELECT generate_series(1, 5) AS nums;
-- [1, 2, 3, 4, 5] (a single LIST, since it's in the SELECT list)
SELECT * FROM generate_series(1, 5) AS t(n);
-- 5 rows: 1, 2, 3, 4, 5
Related terms
Time series data analysis requires special capabilities in a database to analyze data changes over time. It's useful in many scenarios: business planning, product analytics, IoT and operational data analysis.
Sequence →A sequence is a database object that generates a series of unique numeric values, most commonly used to auto-generate primary key IDs.
DATE_TRUNC →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.
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.
EXTRACT →EXTRACT(field FROM value) pulls a single numeric component — such as year, month, or hour — out of a date, time, or timestamp value.
CROSS JOIN →A CROSS JOIN produces the Cartesian product of two tables, pairing every row from the first table with every row from the second.
FAQS
Both generate a sequence with a start, stop, and step, but generate_series includes the stop value in the result while range excludes it, matching Python's range() semantics.
In DuckDB, generate_series behaves as a table function (producing one row per value) when used in a FROM clause, and as a scalar function (producing a single LIST value) when used in a SELECT list.
