Generating multiple different times over different days with generate

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|
 -------------------------- --------------------------

CodePudding user response：

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;

CodePudding user response：

Actually I've come up with a solution that works but it's a bit ugly and there's probably a better way:

WITH days AS (SELECT GENERATE_SERIES('2022-05-17'::timestamp, '2022-05-18'::timestamp, '1 day'::interval) AS day)
SELECT day                      AS start,
       day   INTERVAL '5 hours' AS finish
FROM days
UNION
SELECT day   INTERVAL '10 hours',
       day   INTERVAL '12 hours'
FROM days
UNION
SELECT day   INTERVAL '20 hours',
       day   INTERVAL '22 hours'
FROM days
ORDER BY start;