如何生成跨午夜的班次小时时间数组？

我需要生成start_of_shift到end_of_shift之间的每个小时时间（包含班次开始和结束的小时）。以下查询在员工班次不跨午夜时运行正常，但无法处理跨午夜的班次：

SELECT 
      employee_id,
      shift_id,
      date_of_shift, --date
      TIME(hour,0,0) AS time,
      start_of_shift, --datetime
      end_of_shift,  --datetime
    FROM shift_table ,UNNEST(GENERATE_ARRAY(0, 23)) AS hour
    WHERE hour BETWEEN EXTRACT(HOUR FROM start_of_shift) AND EXTRACT(HOUR FROM end_of_shift) AND 
      date_of_shift = "2023-05-04" 
      AND employee_id = 111111
    ORDER BY 2,3

请问能否使用UNNEST(GENERATE_ARRAY(0, 23))实现预期结果（显示跨午夜的班次）？如果不行，最佳解决方案是什么？

注：修改日期范围无济于事。

当前查询结果 #

预期结果 #

可以继续使用UNNEST(GENERATE_ARRAY(0, 23))，只需调整WHERE条件的逻辑，区分班次是否跨午夜的情况：

SELECT 
  employee_id,
  shift_id,
  date_of_shift,
  TIME(hour, 0, 0) AS time,
  start_of_shift,
  end_of_shift
FROM shift_table, UNNEST(GENERATE_ARRAY(0, 23)) AS hour
WHERE 
  date_of_shift = "2023-05-04" 
  AND employee_id = 111111
  AND (
    -- 不跨午夜的班次：小时在开始和结束之间
    (EXTRACT(HOUR FROM start_of_shift) <= EXTRACT(HOUR FROM end_of_shift) 
     AND hour BETWEEN EXTRACT(HOUR FROM start_of_shift) AND EXTRACT(HOUR FROM end_of_shift))
    OR 
    -- 跨午夜的班次：小时大于等于开始小时，或者小于等于结束小时
    (EXTRACT(HOUR FROM start_of_shift) > EXTRACT(HOUR FROM end_of_shift) 
     AND (hour >= EXTRACT(HOUR FROM start_of_shift) OR hour <= EXTRACT(HOUR FROM end_of_shift)))
  )
ORDER BY shift_id, time;

逻辑说明 #

1. 先判断班次是否跨午夜：通过比较start_of_shift和end_of_shift的小时部分，若开始小时大于结束小时，则说明班次跨午夜。
2. 不跨午夜的班次，保留原有的hour BETWEEN逻辑筛选符合条件的小时。
3. 跨午夜的班次，只要小时数大于等于开始小时（如22、23），或者小于等于结束小时（如0、1），就纳入结果。

这样就能同时覆盖两种班次场景，生成所需的所有小时记录。

内容的提问来源于stack exchange，提问作者BoB