PostgreSQL计算带时间戳的两日期小时差且排除周末的实现咨询

核心实现逻辑 #

• 核心原理是逐段拆分两个时间戳覆盖的所有时间区间，通过EXTRACT(DOW FROM 时间戳)函数判断日期对应的星期，直接剔除所有属于周六、周日的时段，再对剩余有效时段做时长累加，最终换算为小时单位。
• 计算过程依托PostgreSQL内置的generate_series函数覆盖所有跨天场景，可避免首尾时间戳落在非自然日边界带来的计算误差。

可落地的实现步骤 #

• 先定义两个输入参数：起始时间戳、结束时间戳，确认二者的先后顺序，避免出现负时长
• 以两个时间戳截断到天的节点为起止范围，生成覆盖范围内的所有连续自然日
• 逐天做两层判断：
  • 若当日是周六（DOW返回值为6）或周日（DOW返回值为0），直接跳过不计入时长
  • 若当日是工作日，计算该自然日和输入时间范围的重叠区间时长，不直接计满24小时，避免把首尾日超出输入范围的时段算进去
• 把所有工作日的重叠区间时长求和，换算为小时单位即可得到最终差值

可运行的参考代码 #

-- 替换WITH块内的两个时间戳值为实际业务参数即可直接运行
WITH input_params AS (
  SELECT
    '2024-05-20 09:15:00'::timestamp AS start_timestamp,
    '2024-05-27 18:30:00'::timestamp AS end_timestamp
),
covered_days AS (
  -- 生成两个时间戳覆盖的所有自然日
  SELECT generate_series(
    date_trunc('day', start_timestamp),
    date_trunc('day', end_timestamp),
    interval '1 day'
  ) AS calendar_day
  FROM input_params
),
valid_segment_duration AS (
  SELECT
    -- 计算当日和输入时间范围的实际重叠时长，避免边界误差
    LEAST(calendar_day + interval '1 day', end_timestamp) 
    - GREATEST(calendar_day, start_timestamp) AS overlap_duration
  FROM covered_days, input_params
  -- 直接过滤周六、周日的时段
  WHERE EXTRACT(DOW FROM calendar_day) NOT IN (0, 6)
)
-- 汇总时长并换算为小时单位
SELECT ROUND(EXTRACT(EPOCH FROM SUM(overlap_duration)) / 3600, 2) AS excluded_weekend_hour_diff
FROM valid_segment_duration;

补充说明：如果业务场景需要同时剔除法定节假日，可以在上述代码的过滤条件中，关联一张维护好的节假日日期维表，匹配命中的节假日日期同样做排除处理即可，不需要修改核心计算逻辑。

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