跨午夜班次的小时段时长拆分问题修复请求

问题背景 #

每位员工拥有唯一标识emp_id，某员工2023-05-07的班次从19:32:00开始，次日01:19:00结束。现有查询在计算跨午夜时段的时长时出现错误：午夜及之后时段的duration_in_hours值错误使用了班次结束时段的时长，本该显示1的时段显示了0.47/0.32。

当前查询结果 #

预期结果 #

问题根源 #

原查询仅通过TIME类型比较shift_start、shift_end和time_shift，忽略了跨午夜时日期的变化。例如00:00:00实际属于次日，但和shift_end（次日01:19:00）的时间比较时，错误触发了TIME_DIFF(EXTRACT(TIME FROM shift_end), time_shift, MINUTE) < 60的条件，导致计算错误。

修复方案 #

修改逻辑，基于生成的generated_timestamp完整时间戳判断当前时段是班次的起始小时、结束小时还是中间完整小时，而非仅比较时间部分：

WITH shift AS (
  SELECT DISTINCT
    date, 
    emp_id,
    TIME(EXTRACT(HOUR FROM generated_timestamp),0,0) AS time_shift,
    shift_start, -- datetime
    shift_end, -- datetime
    generated_timestamp
  FROM `shift_data`,
    UNNEST(generate_timestamp_array(
      TIMESTAMP_TRUNC(TIMESTAMP(shift_start), HOUR), 
      TIMESTAMP(shift_end), 
      INTERVAL 1 HOUR
    )) generated_timestamp
  WHERE date BETWEEN "2023-05-07" AND "2023-05-07"
)
SELECT
  date,
  emp_id,
  time_shift,
  -- 基于完整时间戳判断时长
  SUM(CASE 
        -- 起始小时：计算从shift_start到该小时结束的时长
        WHEN generated_timestamp = TIMESTAMP_TRUNC(TIMESTAMP(shift_start), HOUR) 
          THEN 1 - EXTRACT(MINUTE FROM shift_start) / 60
        -- 结束小时：计算从该小时开始到shift_end的时长
        WHEN generated_timestamp = TIMESTAMP_TRUNC(TIMESTAMP(shift_end), HOUR) 
          THEN EXTRACT(MINUTE FROM shift_end) / 60
        -- 中间完整小时：时长为1
        ELSE 1
      END) AS duration_in_hours
FROM shift
WHERE emp_id = 123
GROUP BY 1,2,3
ORDER BY 
  date, 
  -- 按实际时间顺序排序，避免00:00排在19:00前
  CASE WHEN time_shift = '00:00:00' THEN 24 ELSE EXTRACT(HOUR FROM time_shift) END

说明 #

• 利用generated_timestamp的完整时间戳，精准判断当前时段是班次的起始、结束还是中间小时，彻底避免跨午夜的时间比较错误。
• 排序时新增逻辑，确保00:00:00排在23:00:00之后，符合班次时间顺序。

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