generate_series produces a set of rows from a start to an end by a step. It is the most direct way to get a continuous axis: an integer run from 1 to 100, a day-by-day calendar, or a grid of timestamps you can later attach aggregates to and see the empty periods too.
Integer ranges
In its simplest form the function takes a start, an end and an optional step. Both bounds are inclusive.
SELECT n FROM generate_series(1, 5) AS n;
SELECT n FROM generate_series(0, 100, 10) AS n;
Handy properties:
- The step can be negative:
generate_series(10, 1, -1) counts down.
- A fractional step works for
numeric: generate_series(0, 1, 0.25).
- It is a table function, so you can put it in
FROM and name its column with AS t(col).
A classic use is a numbered grid, for example row numbers for a report or a fixed list of IDs you can LEFT JOIN against.
Date and timestamp calendars
The same syntax works for dates once you pass the step as an interval. This is the canonical calendar recipe.
SELECT d::date AS day
FROM generate_series(
'2024-01-01'::date,
'2024-01-15'::date,
'1 day'
) AS d;
The step is any interval: '1 hour', '15 minutes', '1 month'. An hourly grid for one day:
SELECT ts
FROM generate_series(
'2024-01-01 00:00'::timestamp,
'2024-01-01 23:00'::timestamp,
'1 hour'
) AS ts;
Remember: the bounds are inclusive on both ends. So January 1 through 15 yields 15 rows, not 14. If you want a half-open axis [start, end), subtract one step from the upper bound.
Filling gaps with zeros
The headline use case. A plain GROUP BY over orders only returns days that actually had orders. Days with no sales simply vanish and the chart breaks. The cure is to generate a calendar and LEFT JOIN against it.
SELECT
cal.day::date AS day,
COALESCE(SUM(o.amount), 0) AS revenue
FROM generate_series(
'2024-01-01'::date,
'2024-01-31'::date,
'1 day'
) AS cal(day)
LEFT JOIN orders o
ON o.created_at >= cal.day
AND o.created_at < cal.day + interval '1 day'
AND o.status = 'paid'
GROUP BY cal.day
ORDER BY cal.day;
Now you get an honest 31 rows, and days without orders report 0 instead of disappearing. Two things matter: the calendar sits on the left of the LEFT JOIN, and COALESCE turns the NULL from SUM into a zero.
Step intervals and per-row series
generate_series also shines inside a LATERAL join: for each table row you can expand its own series. Say you want to split each user's onboarding into 3 checkpoints after signup.
SELECT
u.id,
u.created_at + (g.n * interval '7 days') AS checkpoint
FROM users u
CROSS JOIN LATERAL generate_series(1, 3) AS g(n);
Each user now yields three rows: at 7, 14 and 21 days. The same trick builds hourly slots, installment schedules, or pricing tiers.
Gotcha: if the step in generate_series(a, b, step) points the wrong way (for example b < a with a positive step), the function returns zero rows, not an error. The query silently yields an empty result, so double-check the sign of the step and the order of the bounds.
A MySQL workaround
MySQL has no generate_series. Neither does MariaDB, despite the family resemblance: instead of the function it ships the Sequence storage engine, which serves a run through virtual tables named like seq_1_to_100 or seq_0_to_100_step_10. On plain MySQL the portable way to build a number run is a recursive CTE.
WITH RECURSIVE seq AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM seq WHERE n < 31
)
SELECT n FROM seq;
For a calendar, add days inside the recursion:
WITH RECURSIVE cal AS (
SELECT DATE '2024-01-01' AS day
UNION ALL
SELECT day + INTERVAL 1 DAY FROM cal WHERE day < DATE '2024-01-31'
)
SELECT day FROM cal;
By default recursion is capped at 1000 iterations (cte_max_recursion_depth), so long ranges need that raised. ClickHouse is simpler: SELECT arrayJoin(range(1, 6)) or the numbers(100) function. The idea is the same everywhere: materialize the axis up front so empty periods are never lost.
generate_seriesproduces a set of rows from a start to an end by a step. It is the most direct way to get a continuous axis: an integer run from 1 to 100, a day-by-day calendar, or a grid of timestamps you can later attach aggregates to and see the empty periods too.Integer ranges
In its simplest form the function takes a start, an end and an optional step. Both bounds are inclusive.
SELECT n FROM generate_series(1, 5) AS n; -- 1, 2, 3, 4, 5 SELECT n FROM generate_series(0, 100, 10) AS n; -- 0, 10, 20, ... 100Handy properties:
generate_series(10, 1, -1)counts down.numeric:generate_series(0, 1, 0.25).FROMand name its column withAS t(col).A classic use is a numbered grid, for example row numbers for a report or a fixed list of IDs you can
LEFT JOINagainst.Date and timestamp calendars
The same syntax works for dates once you pass the step as an
interval. This is the canonical calendar recipe.SELECT d::date AS day FROM generate_series( '2024-01-01'::date, '2024-01-15'::date, '1 day' ) AS d; -- 2024-01-01 ... 2024-01-15 (15 rows, both bounds included)The step is any interval:
'1 hour','15 minutes','1 month'. An hourly grid for one day:SELECT ts FROM generate_series( '2024-01-01 00:00'::timestamp, '2024-01-01 23:00'::timestamp, '1 hour' ) AS ts;Remember: the bounds are inclusive on both ends. So January 1 through 15 yields 15 rows, not 14. If you want a half-open axis
[start, end), subtract one step from the upper bound.Filling gaps with zeros
The headline use case. A plain
GROUP BYoverordersonly returns days that actually had orders. Days with no sales simply vanish and the chart breaks. The cure is to generate a calendar andLEFT JOINagainst it.SELECT cal.day::date AS day, COALESCE(SUM(o.amount), 0) AS revenue FROM generate_series( '2024-01-01'::date, '2024-01-31'::date, '1 day' ) AS cal(day) LEFT JOIN orders o ON o.created_at >= cal.day AND o.created_at < cal.day + interval '1 day' AND o.status = 'paid' GROUP BY cal.day ORDER BY cal.day;Now you get an honest 31 rows, and days without orders report
0instead of disappearing. Two things matter: the calendar sits on the left of theLEFT JOIN, andCOALESCEturns theNULLfromSUMinto a zero.Step intervals and per-row series
generate_seriesalso shines inside aLATERALjoin: for each table row you can expand its own series. Say you want to split each user's onboarding into 3 checkpoints after signup.SELECT u.id, u.created_at + (g.n * interval '7 days') AS checkpoint FROM users u CROSS JOIN LATERAL generate_series(1, 3) AS g(n);Each user now yields three rows: at 7, 14 and 21 days. The same trick builds hourly slots, installment schedules, or pricing tiers.
Gotcha: if the step in
generate_series(a, b, step)points the wrong way (for exampleb < awith a positive step), the function returns zero rows, not an error. The query silently yields an empty result, so double-check the sign of the step and the order of the bounds.A MySQL workaround
MySQL has no
generate_series. Neither does MariaDB, despite the family resemblance: instead of the function it ships the Sequence storage engine, which serves a run through virtual tables named likeseq_1_to_100orseq_0_to_100_step_10. On plain MySQL the portable way to build a number run is a recursive CTE.WITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 31 ) SELECT n FROM seq;For a calendar, add days inside the recursion:
WITH RECURSIVE cal AS ( SELECT DATE '2024-01-01' AS day UNION ALL SELECT day + INTERVAL 1 DAY FROM cal WHERE day < DATE '2024-01-31' ) SELECT day FROM cal;By default recursion is capped at 1000 iterations (
cte_max_recursion_depth), so long ranges need that raised. ClickHouse is simpler:SELECT arrayJoin(range(1, 6))or thenumbers(100)function. The idea is the same everywhere: materialize the axis up front so empty periods are never lost.