SQL Server 2019中计算扣除休息时间的运行时长

在SQL Server 2019环境下，存在两张业务表：

• Runs表：存储UTC格式的运行起止时间，以及预期时长
• BreakSchedule表：存储本地时间格式的固定休息时段

需求是：给定一个时区作为SQL参数，查询Runs表的每条记录，计算以下值：

1. UTC起止时间的总时长（分钟）
2. 该时间范围与时区转换后的休息时段的重叠总时长
3. 用总时长减去重叠时长，得到实际有效运行时长

示例表结构与数据 #

create table Runs 
(
    id int identity(1,1) primary key,
    dtStartTimeUtc datetime not null,
    dtEndTimeUtc datetime not null,
    nExpectedMinutes int not null
);

create table BreakSchedule 
(
    tStartTimeLocal time not null,
    tEndTimeLocal time not null
);

insert into BreakSchedule (tStartTimeLocal, tEndTimeLocal)
values ('08:30:00', '08:40:00'), ('09:30:00', '10:30:00'), 
       ('17:00:00', '18:00:00'), ('23:30:00', '00:30:00');

/*
  若本地时区为Central Standard Time，对应UTC时段为
  13:30-13:40, 14:30-15:30, 22:00-23:00, 04:30-05:30
*/

insert into Runs (dtStartTimeUtc, dtEndTimeUtc, nExpectedMinutes)
values
  ('2023-10-02 12:00:00', '2023-10-02 13:30:00', 90),
  ('2023-10-02 13:00:00', '2023-10-02 13:35:00', 30),
  ('2023-10-02 13:35:00', '2023-10-02 14:00:00', 20),
  ('2023-10-02 14:15:00', '2023-10-03 05:15:00', 735),
  ('2023-10-02 14:30:00', '2023-10-02 15:00:00', 0)

初始查询代码 #

declare @tz varchar(100) = 'Central Standard Time';

select 
    *, 'should be same value as nExpectedMinutes' [actualminutes]
from  
    Runs

完整实现查询 #

declare @tz varchar(100) = 'Central Standard Time';

WITH RunDetails AS (
    SELECT 
        id,
        dtStartTimeUtc,
        dtEndTimeUtc,
        nExpectedMinutes,
        -- 转换UTC时间到指定时区的本地时间
        CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, dtStartTimeUtc), @tz)) AS dtStartLocal,
        CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, dtEndTimeUtc), @tz)) AS dtEndLocal,
        -- 计算UTC总时长（分钟）
        DATEDIFF(minute, dtStartTimeUtc, dtEndTimeUtc) AS TotalDurationMinutes
    FROM Runs
),
BreakPeriods AS (
    SELECT 
        tStartTimeLocal,
        tEndTimeLocal,
        -- 标记跨天的休息时段（比如23:30-00:30）
        CASE WHEN tEndTimeLocal < tStartTimeLocal THEN 1 ELSE 0 END AS IsOvernight
    FROM BreakSchedule
),
OverlapCalculations AS (
    SELECT 
        rd.id,
        -- 计算单条休息时段与运行时段的重叠时长总和
        SUM(
            -- 非跨天休息的重叠计算
            CASE WHEN bp.IsOvernight = 0 THEN
                DATEDIFF(
                    minute,
                    IIF(rd.dtStartLocal >= CAST(CAST(rd.dtStartLocal AS date) AS datetime) + CAST(bp.tStartTimeLocal AS datetime), 
                        rd.dtStartLocal, 
                        CAST(CAST(rd.dtStartLocal AS date) AS datetime) + CAST(bp.tStartTimeLocal AS datetime)
                    ),
                    IIF(rd.dtEndLocal <= CAST(CAST(rd.dtStartLocal AS date) AS datetime) + CAST(bp.tEndTimeLocal AS datetime), 
                        rd.dtEndLocal, 
                        CAST(CAST(rd.dtStartLocal AS date) AS datetime) + CAST(bp.tEndTimeLocal AS datetime)
                    )
                )
            ELSE 0 END
            +
            -- 跨天休息的第一部分（当天23:30到午夜）
            CASE WHEN bp.IsOvernight = 1 THEN
                DATEDIFF(
                    minute,
                    IIF(rd.dtStartLocal >= CAST(CAST(rd.dtStartLocal AS date) AS datetime) + CAST(bp.tStartTimeLocal AS datetime), 
                        rd.dtStartLocal, 
                        CAST(CAST(rd.dtStartLocal AS date) AS datetime) + CAST(bp.tStartTimeLocal AS datetime)
                    ),
                    IIF(rd.dtEndLocal <= CAST(DATEADD(day, 1, CAST(rd.dtStartLocal AS date)) AS datetime), 
                        rd.dtEndLocal, 
                        CAST(DATEADD(day, 1, CAST(rd.dtStartLocal AS date)) AS datetime)
                    )
                )
            ELSE 0 END
            +
            -- 跨天休息的第二部分（午夜到次日00:30）
            CASE WHEN bp.IsOvernight = 1 THEN
                DATEDIFF(
                    minute,
                    IIF(CAST(DATEADD(day, 1, CAST(rd.dtStartLocal AS date)) AS datetime) >= rd.dtStartLocal, 
                        CAST(DATEADD(day, 1, CAST(rd.dtStartLocal AS date)) AS datetime), 
                        rd.dtStartLocal
                    ),
                    IIF(rd.dtEndLocal <= CAST(DATEADD(day, 1, CAST(rd.dtStartLocal AS date)) AS datetime) + CAST(bp.tEndTimeLocal AS datetime), 
                        rd.dtEndLocal, 
                        CAST(DATEADD(day, 1, CAST(rd.dtStartLocal AS date)) AS datetime) + CAST(bp.tEndTimeLocal AS datetime)
                    )
                )
            ELSE 0 END
        ) AS OverlapMinutes
    FROM RunDetails rd
    CROSS JOIN BreakPeriods bp
    WHERE 
        -- 判断运行时段与休息时段是否存在重叠
        CASE 
            WHEN bp.IsOvernight = 0 THEN
                rd.dtEndLocal > CAST(CAST(rd.dtStartLocal AS date) AS datetime) + CAST(bp.tStartTimeLocal AS datetime)
                AND rd.dtStartLocal < CAST(CAST(rd.dtStartLocal AS date) AS datetime) + CAST(bp.tEndTimeLocal AS datetime)
            ELSE
                rd.dtEndLocal > CAST(CAST(rd.dtStartLocal AS date) AS datetime) + CAST(bp.tStartTimeLocal AS datetime)
                OR rd.dtStartLocal < CAST(DATEADD(day, 1, CAST(rd.dtStartLocal AS date)) AS datetime) + CAST(bp.tEndTimeLocal AS datetime)
        END
    GROUP BY rd.id
)
SELECT 
    rd.id,
    rd.dtStartTimeUtc,
    rd.dtEndTimeUtc,
    rd.nExpectedMinutes,
    rd.TotalDurationMinutes,
    ISNULL(oc.OverlapMinutes, 0) AS TotalBreakMinutes,
    -- 实际有效时长 = 总时长 - 重叠休息时长
    rd.TotalDurationMinutes - ISNULL(oc.OverlapMinutes, 0) AS actualminutes
FROM RunDetails rd
LEFT JOIN OverlapCalculations oc ON rd.id = oc.id
ORDER BY rd.id;

关键逻辑说明 #

1. 时区转换：使用SWITCHOFFSET函数将UTC时间转换为指定时区的本地时间，确保休息时段匹配基准正确。
2. 跨天休息处理：针对23:30-00:30这类跨天时段，拆分为当天后半段和次日凌晨两段分别计算重叠时长。
3. 重叠时长计算：通过比较运行时段与休息时段的起止时间，取交集部分计算时长，仅统计真正重叠的时间。
4. 空值处理：用ISNULL确保无重叠的记录不会出现NULL值，保证计算结果准确。

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