PostgreSQL查询指定时段白班与额外班次的最大最小时间差异常

原语句问题分析 #

1. 时间过滤条件不符需求：原语句筛选的是03:00-15:00时段，和你要求的白班5:00-15:00不一致。
2. 字符串比较时间存在隐患：用to_char转换为字符串后比较时间，在边界值判断（如15:00整）时容易出错，应该直接用时间类型进行比较。
3. 未区分班次：原语句只能查询单一时段数据，无法同时获取白班和额外班次的结果。

解决方案 #

方式一：分别查询白班/额外班次的总差值

白班查询（5:00-15:00）

SELECT SUM(gap) AS total_white_shift_gap
FROM (
  SELECT TO_CHAR(time_stamp, 'yyyy/mm/dd') AS day,
         EXTRACT(EPOCH FROM (MAX(time_stamp) - MIN(time_stamp))) / 3600 AS gap
  FROM group_table_debarker
  WHERE time_stamp >= current_date - 2
    AND time_stamp < current_date  -- 等价于 [前天00:00, 当天00:00)，避免包含次日0点记录
    AND time_stamp::time BETWEEN '05:00'::time AND '14:59:59'::time  -- 精确匹配5:00到14:59:59，排除15:00整
  GROUP BY TO_CHAR(time_stamp, 'yyyy/mm/dd')
) AS daily_gaps;

额外班次查询（除5:00-15:00外的所有时段）

SELECT SUM(gap) AS total_extra_shift_gap
FROM (
  SELECT TO_CHAR(time_stamp, 'yyyy/mm/dd') AS day,
         EXTRACT(EPOCH FROM (MAX(time_stamp) - MIN(time_stamp))) / 3600 AS gap
  FROM group_table_debarker
  WHERE time_stamp >= current_date - 2
    AND time_stamp < current_date
    AND time_stamp::time NOT BETWEEN '05:00'::time AND '14:59:59'::time
  GROUP BY TO_CHAR(time_stamp, 'yyyy/mm/dd')
) AS daily_gaps;

方式二：一次性查询两个班次的总差值

SELECT
  shift_type,
  SUM(daily_gap) AS total_gap_hours
FROM (
  SELECT
    TO_CHAR(time_stamp, 'yyyy/mm/dd') AS day,
    CASE
      WHEN time_stamp::time BETWEEN '05:00'::time AND '14:59:59'::time THEN '白班'
      ELSE '额外班次'
    END AS shift_type,
    EXTRACT(EPOCH FROM (MAX(time_stamp) - MIN(time_stamp))) / 3600 AS daily_gap
  FROM group_table_debarker
  WHERE time_stamp >= current_date - 2
    AND time_stamp < current_date
  GROUP BY day, shift_type
) AS daily_shift_data
GROUP BY shift_type;

关键优化点 #

• 时间类型直接比较：用time_stamp::time将时间戳转换为时间类型，结合BETWEEN进行精确的时段筛选，避免字符串比较的误差。
• 严谨的时间范围：使用time_stamp < current_date替代time_stamp <= current_date -1，确保不会包含current_date -1当天24:00（即次日00:00）的记录。
• 按班次分组统计：方式二通过CASE标记班次，先按日期+班次计算每日差值，再汇总每个班次的总时长，一次查询得到两个结果。

内容的提问来源于stack exchange，提问作者Uy -Ryan- Huynh