1

I know we can use Postgres's generate_series() function to produce a list of timestamps. Can we do the same to produce a list 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);
1
  • Hoping to avoid having to call tstzrange() in the SELECT clause to not repeat the “1 days” offset twice which in my real example is a more complicated function.
    – eliangius
    Jun 7, 2022 at 11:35

1 Answer 1

1

Up to Postgres 14, 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:

You can produce timestamp or timestamptz, but not date or tstzrange.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.