在Impala中计算两个时间戳之间的工作时长方法

工作时间规则 #

• 周一至周四：每日有效工作时长为 8小时（9:00 - 17:00）
• 周五：每日有效工作时长为 4小时（9:00 - 13:00）
• 周六、周日无有效工作时长

实现方案 #

以下SQL通过拆分日期范围、计算每日有效工作时长，最终汇总得到两个时间戳间的总工作时长：

WITH date_range AS (
    -- 生成起始到结束日期的所有连续日期
    SELECT date_add(@starttimestamp, pos) AS work_date
    FROM (SELECT pos FROM posexplode(sequence(0, datediff(@endtimestamp, @starttimestamp)))) t
),
daily_work_periods AS (
    SELECT 
        work_date,
        -- 确定当日工作时间的结束点
        CASE dayofweek(work_date)
            WHEN 6 THEN timestamp(concat(work_date, ' 13:00:00'))
            ELSE timestamp(concat(work_date, ' 17:00:00'))
        END AS work_day_end,
        -- 确定当日工作时间的起始点
        timestamp(concat(work_date, ' 09:00:00')) AS work_day_start,
        -- 标记是否为起始/结束日期
        work_date = date(@starttimestamp) AS is_start_day,
        work_date = date(@endtimestamp) AS is_end_day
    FROM date_range
),
daily_effective_hours AS (
    SELECT
        -- 计算当日实际有效工作时长（小时）
        CASE
            WHEN day_start > day_end THEN 0
            ELSE (unix_timestamp(day_end) - unix_timestamp(day_start)) / 3600
        END AS effective_hours
    FROM (
        SELECT
            -- 当日实际有效起始时间：取工作时段开始和输入起始时间的较大值
            CASE WHEN is_start_day THEN greatest(@starttimestamp, work_day_start) ELSE work_day_start END AS day_start,
            -- 当日实际有效结束时间：取工作时段结束和输入结束时间的较小值
            CASE WHEN is_end_day THEN least(@endtimestamp, work_day_end) ELSE work_day_end END AS day_end
        FROM daily_work_periods
    ) t
)
SELECT SUM(effective_hours) AS total_work_hours
FROM daily_effective_hours;

关键逻辑说明 #

1. 生成日期范围：通过sequence和posexplode函数生成两个时间戳之间的所有连续日期，覆盖首尾两天。
2. 确定每日工作时段：根据日期的星期类型（dayofweek，周一为2，周五为6），设置对应的工作起止时间。
3. 计算当日有效时长：针对首尾日期，取输入时间与工作时段的交集作为有效工作区间；中间完整工作日直接取对应时长。
4. 汇总时长：将所有日期的有效时长求和，得到总工作时长（单位：小时，支持小数）。

注意事项 #

• 确保@starttimestamp和@endtimestamp为Impala的TIMESTAMP类型，若为字符串需先通过CAST(xxx AS TIMESTAMP)转换。
• 若两个时间戳完全不在工作时段内，结果返回0；若部分重叠，仅计算重叠区间的时长。

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