In PostgreSQL, how do you use generate_series to return a row for every minute of a trading session, including minutes without ticks?
answer
- set-returning row generator
- interval step between timestamps
- LEFT JOIN the bucketed data
- no date overload exists
- time zone argument for days
basics
~10 sgenerate_series(start, stop, interval '1 minute') over timestamptz produces one row per minute; LEFT JOIN the ticks aggregated by date_trunc('minute', traded_at) to it, so minutes without trades appear with NULL values.
solid answer
~40 s`generate_series(start, stop, step)` is a set-returning function: with `timestamptz` bounds and `interval '1 minute'` it yields every minute of the session, which you `LEFT JOIN` to ticks grouped by `date_trunc('minute', traded_at)`. Empty minutes then appear with NULL aggregates instead of vanishing. It also has integer, bigint and numeric forms, returns zero rows when the step points away from `stop` or an input is NULL, and raises an error for a zero step. There is **no date form**: date arguments resolve to the `timestamptz` version, so results depend on the session `TimeZone`. For day steps across a daylight-saving change, pass the fourth argument, such as `'America/New_York'`, so each day keeps its local time.
code
sql · 3 linesSELECT d::date AS session_date
FROM generate_series(date '2026-10-01', date '2026-10-09', interval '1 day') AS d
WHERE extract(isodow FROM d) < 6;go deeper
Recall that generate_series produces one row per value between start and stop, for integers, numerics and timestamps with an interval step.
Explain the LEFT JOIN gap-filling pattern, the zero-row and error edge cases, and why date inputs come back as timestamptz.
Show you have handled daylight-saving bugs in time-bucketed reports by passing the exchange's time zone and keeping bucket and series boundaries consistent.
Decide whether gap filling belongs in SQL or in the charting layer, weighing row counts, time-zone ownership and how many consumers need the same calendar.
## What generate_series produces `generate_series` is a **set-returning function**: it is used in `FROM` like a table and produces one row per value. PostgreSQL defines these forms: | Arguments | Returns | Notes | |---|---|---| | `(integer, integer [, integer])` | `setof integer` | Step defaults to 1 | | `(bigint, bigint [, bigint])` | `setof bigint` | Step defaults to 1 | | `(numeric, numeric [, numeric])` | `setof numeric` | `generate_series(1.1, 4, 1.3)` gives 1.1, 2.4, 3.7 | | `(timestamp, timestamp, interval)` | `setof timestamp` | No time zone | | `(timestamptz, timestamptz, interval [, text])` | `setof timestamptz` | Optional time zone name | Edge rules: - A positive step with `start > stop`, or a negative step with `start < stop`, returns **zero rows**. - Any NULL input returns zero rows. - A step of zero is an **error**. ## Filling gaps in minute bars A chart of one symbol's trading day needs a bar for every minute, but `GROUP BY date_trunc('minute', traded_at)` returns only minutes that had trades. Generate the full set of minutes and outer-join the data to it: ```sql WITH bars AS ( SELECT date_trunc('minute', traded_at) AS minute, min(price) AS low, max(price) AS high, count(*) AS trades FROM ticks WHERE symbol = 'ACME' AND traded_at >= timestamptz '2026-10-05 09:30 America/New_York' AND traded_at < timestamptz '2026-10-05 16:00 America/New_York' GROUP BY 1 ) SELECT m.minute, b.low, b.high, coalesce(b.trades, 0) AS trades FROM generate_series(timestamptz '2026-10-05 09:30 America/New_York', timestamptz '2026-10-05 15:59 America/New_York', interval '1 minute') AS m(minute) LEFT JOIN bars b ON b.minute = m.minute ORDER BY m.minute; ``` Key points: 1. The series supplies the **complete** set of rows; the `LEFT JOIN` keeps minutes with no ticks. 2. `coalesce` turns a missing count into 0; for prices, NULL is usually the honest value. 3. Filtering `ticks` on the same window keeps the aggregate small. ## Time-zone-aware steps For `timestamptz`, adding `interval '1 day'` is computed in a time zone, and across a daylight-saving change a day is 23 or 25 hours long. The four-argument form names that zone explicitly: ```sql SELECT generate_series(timestamptz '2026-10-30 09:30 America/New_York', timestamptz '2026-11-04 09:30 America/New_York', interval '1 day', 'America/New_York'); ``` Each result is 09:30 New York time, even after the clocks change. Without the argument, the session's `TimeZone` setting is used, so a server set to UTC would step in 24-hour UTC days and drift by an hour in local time. ## Traps - **No date overload.** `generate_series('2026-10-01'::date, '2026-10-31'::date, interval '1 day')` resolves to the `timestamptz` form, because `timestamptz` is the preferred datetime type. The result is `timestamptz` at midnight in the session zone; cast back with `::date`, or use `current_date + generate_series(0, 30)` to stay in dates. - **Calendar steps.** An interval such as `'1 month'` is added to the previous value each time, so a series starting on 31 January lands on 28 February and stays on the 28th afterwards. - **Mismatched buckets.** The series and the `date_trunc` buckets must use the same boundaries; a series that starts at 09:30:30 never equals a bucket that starts at 09:30:00, and every join row comes back empty. - **In the select list**, a set-returning function multiplies the row count; use it in `FROM` when you mean a row source. - **Huge ranges** are generated row by row; a year of seconds is 31.5 million rows. ## Other uses - Synthetic test data: `INSERT INTO ticks (symbol, traded_at, price) SELECT 'TEST', now() - g * interval '1 second', 100 FROM generate_series(1, 100000) AS g;` - Calendars for reports, so days without sales appear. - `generate_subscripts(array, dim)` is the related function that yields the valid subscripts of an array. - Numbered batches: `generate_series(0, 9)` can drive ten parallel jobs, each taking the rows whose `id % 10` matches its number. An interviewer listening for depth wants the `LEFT JOIN` gap-filling pattern, the result type of date inputs, and the time-zone argument.
- What type does generate_series(date '2026-10-01', date '2026-10-09', interval '1 day') return?`timestamp with time zone`. PostgreSQL has no date overload, and when resolving the call it prefers `timestamptz`, the preferred type of the datetime category. Each value is midnight in the session `TimeZone`; cast with `::date` or add integers from `generate_series(0, n)` to a date to stay in the date type.
- Why might a one-day timestamptz series drift by an hour in local time?Day arithmetic on `timestamptz` follows a time zone's daylight-saving rules. Without the fourth argument PostgreSQL uses the session `TimeZone`; if that is UTC, each step is 24 hours, so a local 09:30 becomes 08:30 or 10:30 after a clock change. Passing the exchange's zone keeps local time fixed.
- GROUP BY date_trunc returns a row for every minute, even without ticks.
- generate_series has a date overload that returns dates.
- A day step in timestamptz is always exactly 24 hours.
- A zero step returns an empty set rather than an error.
- generate_series needs a helper calendar table to produce rows.