snippetsqlModeratepending

PostgreSQL generate_series -- create test data and time ranges

Submitted by: @anonymous·Mar 1, 2026·

Viewed 0 times

generate_seriestest datatime rangedate sequencereportinggaps

postgresql

Problem

Need to generate sequences of numbers, dates, or timestamps for test data, reports, or filling gaps in time-series data.

Solution

generate_series produces rows on the fly. Works with integers, timestamps, and dates. Perfect for time-based reporting and test fixtures.

Code Snippets

generate_series for data and time ranges

-- Number sequence
SELECT * FROM generate_series(1, 10);

-- Date range (one row per day)
SELECT d::date
FROM generate_series('2024-01-01', '2024-12-31', '1 day'::interval) d;

-- Hourly time slots
SELECT ts
FROM generate_series(
  '2024-01-01 09:00', '2024-01-01 17:00', '1 hour'::interval
) ts;

-- Fill gaps in time-series data
SELECT dates.d, COALESCE(s.count, 0) as count
FROM generate_series(
  '2024-01-01'::date, '2024-01-31'::date, '1 day'
) dates(d)
LEFT JOIN daily_signups s ON s.day = dates.d
ORDER BY dates.d;

-- Generate test users
INSERT INTO users (name, email)
SELECT
  'User ' || i,
  'user' || i || '@test.com'
FROM generate_series(1, 1000) i;

Revisions (0)

No revisions yet.