Creates rows with a sequence of numbers or points in time.
GENERATE_SERIES(<start>, <stop>)
GENERATE_SERIES(<start>, <stop>, <step>)
Generates rows from the start to the end inclusive with the given step: the numbers 1, 2, 3… or dates day by day. No table is needed for it. Most often used to get a complete calendar and show even the days or months when nothing happened in a report.
orders
| id | created_at | amount |
|---|---|---|
| 1 | 2026-07-03 10:15:00 | 120.50 |
| 2 | 2026-07-28 18:40:00 | 80.25 |
| 3 | 2026-08-09 09:05:00 | 199.99 |
| 4 | 2026-08-21 14:30:00 | 45.00 |
| 5 | 2026-09-02 11:00:00 | 64.75 |
These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.
| n |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| percent |
|---|
| 0 |
| 25 |
| 50 |
| 75 |
| 100 |
| day |
|---|
| 2026-09-07 |
| 2026-09-08 |
| 2026-09-09 |
| 2026-09-10 |
| 2026-09-11 |
| 2026-09-12 |
| 2026-09-13 |
| month | order_count |
|---|---|
| 2026-06-01 | 0 |
| 2026-07-01 | 2 |
| 2026-08-01 | 2 |
| 2026-09-01 | 1 |
There were no orders in June, but the month is in the report: LEFT JOIN keeps it, and COUNT(o.id) gives 0.
The end is included in the result: GENERATE_SERIES(1, 5) gives 5 rows.
If the start is greater than the end and the step is positive, there are no rows at all. To count backwards, use a negative step: GENERATE_SERIES(5, 1, -1).
For dates, an interval step is required. And if you pass a DATE, the result becomes timestamp with time zone and depends on the time zone — that's why the examples use TIMESTAMP and ::date.