skip to content

In PostgreSQL, how do you use generate_series to return a row for every minute of a trading session, including minutes without ticks?

level: middleimportance: nice to knowfreq 30%

answer

  1. set-returning row generator
  2. interval step between timestamps
  3. LEFT JOIN the bucketed data
  4. no date overload exists
  5. time zone argument for days

basics

~10 s

generate_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 lines
sql
SELECT 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

for a junior

Recall that generate_series produces one row per value between start and stop, for integers, numerics and timestamps with an interval step.

for a middle

Explain the LEFT JOIN gap-filling pattern, the zero-row and error edge cases, and why date inputs come back as timestamptz.

for a senior

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.

for a principal

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.