generate_series() for Sequences and Date Spines in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this Date Arithmetic and Intervals, LEFT JOIN and RIGHT JOIN, Common Table Expressions (CTEs)
Builds toward Recursive CTEs, Date Spine Construction and Zero-Fill Patterns
What is generate_series in SQL?
generate_series() produces a sequence of values as rows — dates, timestamps, or integers — that you can query like a table. Its primary use in analytics is building a complete list of every date in a range, so that gaps in your data show up as zeros instead of disappearing from your results.
Here's the problem it solves. Your orders table only has rows for days when orders happened. Group by day and you get a result with no row for quiet days — they simply don't exist in the data. If you need those days to appear with a count of 0, there's nothing to GROUP BY. You need to create those rows synthetically.
How do you build a date spine in PostgreSQL?
generate_series() creates them. It takes a start, an end, and a step:
SELECT generate_series(
'2024-01-01'::date,
'2024-12-31'::date,
'1 day'::interval
) AS dayThis produces 366 rows, one per calendar day in 2024. The function expands into rows in FROM like a table. From here, the standard pattern is to wrap it in a CTE and LEFT JOIN to your fact data:
WITH date_spine AS (
SELECT generate_series(
'2024-01-01'::date,
'2024-12-31'::date,
'1 day'::interval
)::date AS day
)
SELECT
ds.day,
COALESCE(COUNT(o.id), 0) AS order_count
FROM date_spine ds
LEFT JOIN orders o ON o.ordered_at::date = ds.day
GROUP BY ds.day
ORDER BY ds.dayThe LEFT JOIN keeps every generated day regardless of whether orders exist. Days with no orders produce NULL in o.id, COUNT ignores NULLs, and COALESCE converts the resulting NULL count to 0. Every day in the range appears in the output.
Here's a monthly version you can run on the ecommerce data:
SELECT generate_series('2024-01-01'::date, '2024-06-01'::date, interval '1 month')::date AS monthWhy does generate_series return timestamps instead of dates?
The one thing that trips people up
When you pass date inputs with an interval step, PostgreSQL returns timestamps, not dates. The ::date cast inside the CTE converts each value back to a plain date. Without it the spine column stays a timestamp — it prints as 2024-01-01 00:00:00+00 and carries a time of day. Joining that to a date column still matches, because PostgreSQL promotes the date to midnight. What finds nothing is a timestamp column recording a real time of day: 2024-01-02 14:30 is not midnight. That is why the fact side above is cast with o.ordered_at::date.
How do you generate a weekly or monthly series?
The step controls the resolution. Change '1 day' to '1 week' or '1 month' for weekly or monthly spines. For monthly sequences, PostgreSQL applies calendar arithmetic: adding one month to January 31 gives February 28 (or 29 in a leap year), not March 2. The arithmetic follows real calendar logic, so month-end dates compress forward to the last day of the next month.
generate_series() works with integers too: generate_series(1, 100, 1) produces 100 rows numbered 1 through 100. The date spine pattern is the most common analytical use, but integer sequences appear in test data generation and row-numbering problems as well.
Should generate_series go in FROM or SELECT?
generate_series() in FROM vs in SELECT
You can call generate_series() directly in the FROM clause without wrapping it in a CTE, but the CTE approach is cleaner and gives the sequence a named column. Calling it in the SELECT list is also valid for simple cases: SELECT generate_series(1, 5) returns 5 rows with values 1 through 5. When used in FROM alongside other tables, always wrap it in a CTE or subquery with an explicit column alias — the raw function call produces a column named generate_series, which is awkward to join on.
Practice generate_series in SQL
Brightlane's QA system assigns consecutive numeric identifiers to each test run in a processing batch.
Write a query to return a sequence of integers from 1 through 5, with each value appearing as a separate row.
Output:
- Five rows, with one column,
test_id, containing the integers1through5in ascending order.
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 generate_series practice problems
Write a query to return a sequence of integers from 1 through 5, with each value appearing as a separate row.
Write a query to return the first day of each calendar month from January through June 2024, one date per row.
Write a query to return a sequence covering every day from '2024-01-08' through '2024-01-14', one date per row.
Write a query to return every date from '2024-01-01' through '2024-01-07' alongside the number of orders placed on that date. Days with no orders should appear with an order count of 0.
Write a query to return every date in that five-day range and its total order revenue. Days with no orders should appear with a missing revenue value.
Write a query to return every date from '2024-01-01' through '2024-01-07' alongside the number of events recorded that day. Days with no events should appear with a count of 0.
Write a query to return every integer from 1 through 10 alongside the customer name if a customers record with that id is on file, or a missing value if no record was found.
Write a query to return the total number of rows produced when generate_series is called with a start of '2024-03-01', an end of '2024-02-01', and a step of 1 day.
Write a query to return all renewal dates produced by generate_series with a start of '2024-01-31', an end of '2024-04-30', and a step of '1 month'. Each result should be cast back to DATE.
Write a query to return every date from '2024-01-01' through '2024-01-14' for which no order is on record.
Start learning to practice all 10 generate_series problems, with instant grading and mastery tracking.
Common questions about generate_series
Does generate_series include the end value?
Yes, when the step lands on it exactly. generate_series(1, 5) returns five rows, 1 through 5. With a step that overshoots, it stops at the last value that does not pass the end: counting from 1 to 10 in steps of 4 returns 1, 5 and 9.
Can generate_series produce a list of dates?
Yes. Give it a start, an end and an INTERVAL step, and it returns one row per step. With date bounds the rows come back as timestamptz values rather than dates, so cast them with ::date when you want plain calendar days to join against.
Why use generate_series instead of the dates already in my table?
Because your table only contains dates on which something happened. A day with no orders has no row to group, so grouping that table by day skips it entirely. generate_series produces every value in the range whether or not anything happened on it.
What happens if the start value is greater than the end?
With a positive step you get no rows and no error. To count downward, give it a negative step: generate_series(5, 1, -1) returns 5, 4, 3, 2, 1.
