Snowflake中如何计算两个日期间精确到分钟的工作时长

前置依赖 #

• 已存在日期维度表dim_date，包含calendar_date（DATE类型，存储自然日）、is_workday（NUMBER(1,0)类型，1=工作日，0=周末/节假日）两个核心字段
• 固定工作时段规则：工作日有效计算区间为每日8:00-16:00，单日满额有效时长480分钟
• 需自动适配起止时间落在非工作时段、跨非工作日/节假日的边界场景

核心计算逻辑 #

将总时长拆分为三部分独立计算后求和，避免复杂嵌套判断出错：

• 起始日有效分钟：若起始日为工作日，取「起始时间、当日8:00」的较大值作为计算起点，当日16:00作为计算终点，计算两者分钟差；若起始日为非工作日，或起点晚于当日16:00，当日计0
• 中间完整日有效分钟：筛选日期严格大于起始日、严格小于结束日且is_workday=1的所有自然日，每个自然日固定计480分钟后求和
• 结束日有效分钟：若结束日为工作日，取当日8:00作为计算起点，「结束时间、当日16:00」的较小值作为计算终点，计算两者分钟差；若结束日为非工作日，或终点早于当日8:00，当日计0

边界适配规则：起止时间落在当日8:00前自动对齐到8:00，落在当日16:00后自动对齐到16:00，非工作日直接不计入时长。

可直接落地的Snowflake代码 #

单次查询写法 #

针对示例场景（计算2022-06-20 07:32:00到2022-06-24 17:42:00的工作分钟差，其中2022-06-23为节假日），可直接运行以下SQL：

WITH params AS (
    SELECT
        '2022-06-20 07:32:00'::TIMESTAMP_NTZ AS start_ts,
        '2022-06-24 17:42:00'::TIMESTAMP_NTZ AS end_ts
),
calc_result AS (
    SELECT
        -- 计算起始日有效分钟
        CASE
            WHEN d1.is_workday = 1
            THEN DATEDIFF(
                'minute',
                GREATEST(start_ts, DATE_TRUNC('day', start_ts) + INTERVAL '8 hour'),
                DATE_TRUNC('day', start_ts) + INTERVAL '16 hour'
            )
            ELSE 0
        END
        +
        -- 计算中间完整工作日有效分钟
        (
            SELECT COUNT(1)*480
            FROM dim_date d
            WHERE d.calendar_date > DATE_TRUNC('day', p.start_ts)::DATE
              AND d.calendar_date < DATE_TRUNC('day', p.end_ts)::DATE
              AND d.is_workday = 1
        )
        +
        -- 计算结束日有效分钟
        CASE
            WHEN d2.is_workday = 1
            THEN DATEDIFF(
                'minute',
                DATE_TRUNC('day', end_ts) + INTERVAL '8 hour',
                LEAST(end_ts, DATE_TRUNC('day', end_ts) + INTERVAL '16 hour')
            )
            ELSE 0
        END AS total_work_minutes
    FROM params p
    LEFT JOIN dim_date d1 ON d1.calendar_date = DATE_TRUNC('day', p.start_ts)::DATE
    LEFT JOIN dim_date d2 ON d2.calendar_date = DATE_TRUNC('day', p.end_ts)::DATE
)
SELECT total_work_minutes FROM calc_result;

上述示例执行后返回结果为1920，与手动计算结果一致：6月20日、21日、22日、24日为工作日，每日均计满480分钟，6月23日为节假日不计入，总时长4*480=1920分钟。

复用函数封装 #

如果需要频繁调用该计算逻辑，可封装为自定义函数：

CREATE OR REPLACE FUNCTION calc_work_minutes(start_ts TIMESTAMP_NTZ, end_ts TIMESTAMP_NTZ)
RETURNS NUMBER
AS
$$
WITH calc_components AS (
    SELECT
        CASE
            WHEN d1.is_workday = 1
            THEN DATEDIFF(
                'minute',
                GREATEST(start_ts, DATE_TRUNC('day', start_ts) + INTERVAL '8 hour'),
                DATE_TRUNC('day', start_ts) + INTERVAL '16 hour'
            )
            ELSE 0
        END
        +
        (
            SELECT COUNT(1)*480
            FROM dim_date d
            WHERE d.calendar_date > DATE_TRUNC('day', start_ts)::DATE
              AND d.calendar_date < DATE_TRUNC('day', end_ts)::DATE
              AND d.is_workday = 1
        )
        +
        CASE
            WHEN d2.is_workday = 1
            THEN DATEDIFF(
                'minute',
                DATE_TRUNC('day', end_ts) + INTERVAL '8 hour',
                LEAST(end_ts, DATE_TRUNC('day', end_ts) + INTERVAL '16 hour')
            )
            ELSE 0
        END AS total_minutes
    LEFT JOIN dim_date d1 ON d1.calendar_date = DATE_TRUNC('day', start_ts)::DATE
    LEFT JOIN dim_date d2 ON d2.calendar_date = DATE_TRUNC('day', end_ts)::DATE
)
SELECT total_minutes FROM calc_components
$$;

-- 调用示例
SELECT calc_work_minutes('2022-06-20 07:32:00', '2022-06-24 17:42:00');

适配说明 #

• 若实际日期维度表的表名、字段名与示例不一致，替换SQL中对应的标识符即可
• 若后续工作时段规则调整，仅需修改SQL中固定的8 hour、16 hour时间参数，以及单日满额分钟数（当前为480）即可快速适配
• 逻辑默认支持起止时间为同一天、起止时间均落在非工作时段、跨长周期节假日等场景，无需新增额外判断

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