如何在BigQuery中计算排除周末、仅5AM-6PM的Timestamp分钟时间差

不需要额外创建周末日期表，用BigQuery内置函数就能实现需求。核心思路是把时间范围拆成开始日期的有效时长、中间完整日期的有效时长总和、结束日期的有效时长三部分，分别计算后累加。

实现逻辑 #

1. 定义有效规则：每天仅统计05:00:00到18:00:00的时段，单个有效工作日的总分钟数为13*60=780分钟；BigQuery中EXTRACT(DAYOFWEEK FROM date)返回1代表周日、7代表周六，这两天不计入有效时长。
2. 拆分计算单元：
   • 开始日期：仅统计当天有效时段内从start_time到时段结束的时长（若start_time在时段外则为0）
   • 结束日期：仅统计当天有效时段内从时段开始到end_time的时长（若end_time在时段外则为0）
   • 中间日期：动态生成开始与结束日期之间的所有日期，过滤掉周末后累加每个工作日的有效时长

完整SQL示例 #

假设你的表名为your_table，包含start_time和end_time两个TIMESTAMP列：

WITH date_boundaries AS (
  SELECT
    start_time,
    end_time,
    DATE(start_time) AS start_date,
    DATE(end_time) AS end_date,
    TIME '05:00:00' AS valid_start,
    TIME '18:00:00' AS valid_end,
    13 * 60 AS daily_valid_mins
  FROM your_table
),
single_day_calc AS (
  SELECT
    *,
    -- 计算开始日期的有效时长
    CASE
      WHEN EXTRACT(DAYOFWEEK FROM start_date) IN (1,7) THEN 0
      ELSE GREATEST(0, TIME_DIFF(
        LEAST(IF(start_date = end_date, TIME(end_time), valid_end), valid_end),
        GREATEST(TIME(start_time), valid_start),
        MINUTE
      ))
    END AS start_day_mins,
    -- 计算结束日期的有效时长（仅跨天时生效）
    CASE
      WHEN start_date = end_date THEN 0
      WHEN EXTRACT(DAYOFWEEK FROM end_date) IN (1,7) THEN 0
      ELSE GREATEST(0, TIME_DIFF(
        LEAST(TIME(end_time), valid_end),
        valid_start,
        MINUTE
      ))
    END AS end_day_mins
  FROM date_boundaries
),
middle_days_calc AS (
  SELECT
    start_time,
    end_time,
    SUM(CASE WHEN EXTRACT(DAYOFWEEK FROM date) NOT IN (1,7) THEN daily_valid_mins ELSE 0 END) AS middle_mins
  FROM date_boundaries
  CROSS JOIN UNNEST(GENERATE_DATE_ARRAY(start_date + INTERVAL 1 DAY, end_date - INTERVAL 1 DAY)) AS date
  GROUP BY start_time, end_time
)
SELECT
  start_time,
  end_time,
  start_day_mins + end_day_mins + COALESCE(middle_mins, 0) AS total_valid_mins
FROM single_day_calc
LEFT JOIN middle_days_calc USING(start_time, end_time);

验证示例场景 #

对于你给出的测试用例：start_time = '2023-01-02 17:58:00 UTC'，end_time = '2023-01-03 05:01:00 UTC'

• 开始日期（2023-01-02，周一）有效时长：18:00 - 17:58 = 2分钟
• 结束日期（2023-01-03，周二）有效时长：05:01 - 05:00 = 1分钟
• 无中间日期
• 总时长：2 + 1 = 3分钟，与预期结果一致

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