PostgreSQL generate_series: Build Date Spines and Gap-Free Reports
By Sharon Ben-Moshe · October 8, 2026 · 1 min read
Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.
Learn how to use PostgreSQL generate_series() to build date spines, generate timestamp ranges, and create gap-free analytics reports.
Quick answer
PostgreSQL's generate_series(start, stop, step) is a set-returning function that generates a series of numbers or timestamps. In analytics, it is primarily used to build a 'calendar date spine' that is LEFT JOINed with sparse transactional data, ensuring that days with zero sales or zero signups still appear in reports with a count of 0.
FAQs
How do I generate daily dates between two points in PostgreSQL?
Run `SELECT generate_series('2026-01-01'::date, '2026-01-31'::date, '1 day'::interval)::date AS day;`.
Why are date spines critical for charting and reporting?
If an e-commerce store has no orders on Sunday, a simple `GROUP BY order_date` omits Sunday completely. A date spine guarantees all 7 days exist so graphs render continuous timelines.
Does MySQL have an equivalent to generate_series?
MySQL does not have a native generate_series function. In MySQL 8.0+, developers use a recursive CTE to generate date spines.
Can generate_series generate numeric sequences?
Yes! `SELECT * FROM generate_series(1, 100, 5)` generates integers 1, 6, 11, etc., up to 100.
Keep reading
The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability
By Sharon Ben-Moshe · Oct 13, 2026
The complete guide to SQL data types across PostgreSQL, MySQL, SQL Server, and SQLite. Numeric precision, character sets, timestamps, and JSON.
Read postThe Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes
By Sharon Ben-Moshe · Oct 13, 2026
The comprehensive guide to SQL window functions. Master OVER, PARTITION BY, ORDER BY, window frames (ROWS vs RANGE), and ranking recipes.
Read postTry it now
Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.