Generate series of time ranges

Question

I know we can use Postgres' generate_series() function to produce a set of timestamps. Can we do the same to produce a set of time ranges directly without having to manually convert generated timestamps into time ranges like so?

SELECT tstzrange(
   time_stamp,
   time_stamp + '1 days'
   '[)'
) AS time_range
FROM generate_series(
   '2022-01-01'::TIMESTAMPTZ,
   '2022-02-01'::TIMESTAMPTZ,
   '1 days'
) tmp(time_stamp);

Answer 1

You can produce timestamp or timestamptz, but not date or tstzrange. Up to Postgres 17, there is no variant of generate_series() doing that. Check with:

SELECT oid::regprocedure   AS function_signature
     , prorettype::regtype AS return_type
FROM   pg_proc
where  proname ~ 'generate_series';

See:

• Generating time series between two dates in PostgreSQL

Aside, '[)' (incl. lower & excluding upper bound) is the default and can safely be omitted:

SELECT tstzrange(ts, ts + interval '1 day') AS my_range
FROM   generate_series(timestamptz '2022-01-01'
                     , timestamptz '2022-02-01'
                     , interval '1 day') t(ts);