postgresqlgenerate-series

Generating multiple different times over different days with generate_series

I am trying to generate different length time intervals e.g. 0-5am, 10am-1pm, 6-8pm that occur daily, so something like this:

+--------------------------+--------------------------+
|start                     |finish                    |
+--------------------------+--------------------------+
|2022-05-17 00:00:00.000000|2022-05-17 05:00:00.000000|
|2022-05-17 10:00:00.000000|2022-05-17 13:00:00.000000|
|2022-05-17 18:00:00.000000|2022-05-17 20:00:00.000000|
|2022-05-18 00:00:00.000000|2022-05-18 05:00:00.000000|
|2022-05-18 10:00:00.000000|2022-05-18 13:00:00.000000|
|2022-05-18 18:00:00.000000|2022-05-18 20:00:00.000000|
+--------------------------+--------------------------+

This is what I have so far but it isn't working because it's generating the product of the two arrays:

SELECT day + a AS start,
       day + b AS finish
FROM GENERATE_SERIES('2022-05-17'::timestamp, '2022-05-18'::timestamp, '1 day'::interval) day,
     UNNEST('{"0 hours", "10 hours", "18 hours"}'::interval[]) a,
     UNNEST('{"5 hours", "13 hours", "20 hours"}'::interval[]) b;

Result:

+--------------------------+--------------------------+
|start                     |finish                    |
+--------------------------+--------------------------+
|2022-05-17 00:00:00.000000|2022-05-17 05:00:00.000000|
|2022-05-17 00:00:00.000000|2022-05-17 13:00:00.000000|
|2022-05-17 00:00:00.000000|2022-05-17 20:00:00.000000|
|2022-05-17 10:00:00.000000|2022-05-17 05:00:00.000000|
|2022-05-17 10:00:00.000000|2022-05-17 13:00:00.000000|
|2022-05-17 10:00:00.000000|2022-05-17 20:00:00.000000|
|2022-05-17 18:00:00.000000|2022-05-17 05:00:00.000000|
|2022-05-17 18:00:00.000000|2022-05-17 13:00:00.000000|
|2022-05-17 18:00:00.000000|2022-05-17 20:00:00.000000|
|2022-05-18 00:00:00.000000|2022-05-18 05:00:00.000000|
|2022-05-18 00:00:00.000000|2022-05-18 13:00:00.000000|
|2022-05-18 00:00:00.000000|2022-05-18 20:00:00.000000|
|2022-05-18 10:00:00.000000|2022-05-18 05:00:00.000000|
|2022-05-18 10:00:00.000000|2022-05-18 13:00:00.000000|
|2022-05-18 10:00:00.000000|2022-05-18 20:00:00.000000|
|2022-05-18 18:00:00.000000|2022-05-18 05:00:00.000000|
|2022-05-18 18:00:00.000000|2022-05-18 13:00:00.000000|
|2022-05-18 18:00:00.000000|2022-05-18 20:00:00.000000|
+--------------------------+--------------------------+
Solution

  • You must unnest two arrays in one select to do it in parallel (both arrays must have the same number of elements)

    
SELECT day + c.a AS start,
       day + c.b AS finish
FROM GENERATE_SERIES('2022-05-17'::timestamp, '2022-05-18'::timestamp, '1 day'::interval) day,
(
  select 
    UNNEST('{"0 hours", "10 hours", "18 hours"}'::interval[]) a, 
    UNNEST('{"5 hours", "13 hours", "20 hours"}'::interval[]) b
) c;