SQL Server中如何计算两个时间间仅有效时段的分钟差值

需求：计算开始时间（StartDate）与结束时间（EndDate）之间的分钟差，但仅统计8:30-13:00和15:00-19:00这两个有效时段内的时长，排除非有效时段。当前使用DATEDIFF(minute, StartDate, EndDate)无法实现该需求。

示例场景 #

StartDate: 2022-12-10 00:00:00.000
EndDate: 2022-12-10 09:00:15.000
Duration: 30 minutes.

StartDate: 2022-12-10 10:00:30.000
EndDate: 2022-12-10 16:00:00.000
Duration: 180 (from 10.00 to 13.00) + 60 (from 15.00 to 16.00) minutes.

StartDate: 2022-12-10 00:00:00.000
EndDate: 2022-12-10 08:30:00.000
Duration: 0 minutes.

StartDate: 2022-12-10 00:08:00.000
EndDate: 2022-12-11 03:00:00.000
Duration: 510 minutes.

解决方案 #

通过生成日期序列覆盖起止时间区间，逐天计算有效时段与实际时间的交集分钟数，最后累加得到总有效时长：

WITH DateRange AS (
    -- 生成StartDate到EndDate之间的所有日期
    SELECT CAST(StartDate AS DATE) AS DateValue
    FROM YourTable
    UNION ALL
    SELECT DATEADD(DAY, 1, DateValue)
    FROM DateRange
    WHERE DATEADD(DAY, 1, DateValue) <= CAST(EndDate AS DATE)
)
SELECT 
    t.StartDate,
    t.EndDate,
    -- 累加每天两个有效时段的交集分钟数
    SUM(
        -- 计算8:30-13:00时段的有效分钟数
        CASE 
            WHEN DATEDIFF(MINUTE, 
                CASE WHEN CAST(t.StartDate AS DATETIME) > dr.DayStart + '08:30:00' THEN CAST(t.StartDate AS DATETIME) ELSE dr.DayStart + '08:30:00' END,
                CASE WHEN CAST(t.EndDate AS DATETIME) < dr.DayEnd + '13:00:00' THEN CAST(t.EndDate AS DATETIME) ELSE dr.DayEnd + '13:00:00' END
            ) > 0 
            THEN DATEDIFF(MINUTE, 
                CASE WHEN CAST(t.StartDate AS DATETIME) > dr.DayStart + '08:30:00' THEN CAST(t.StartDate AS DATETIME) ELSE dr.DayStart + '08:30:00' END,
                CASE WHEN CAST(t.EndDate AS DATETIME) < dr.DayEnd + '13:00:00' THEN CAST(t.EndDate AS DATETIME) ELSE dr.DayEnd + '13:00:00' END
            ) 
            ELSE 0 
        END +
        -- 计算15:00-19:00时段的有效分钟数
        CASE 
            WHEN DATEDIFF(MINUTE, 
                CASE WHEN CAST(t.StartDate AS DATETIME) > dr.DayStart + '15:00:00' THEN CAST(t.StartDate AS DATETIME) ELSE dr.DayStart + '15:00:00' END,
                CASE WHEN CAST(t.EndDate AS DATETIME) < dr.DayEnd + '19:00:00' THEN CAST(t.EndDate AS DATETIME) ELSE dr.DayEnd + '19:00:00' END
            ) > 0 
            THEN DATEDIFF(MINUTE, 
                CASE WHEN CAST(t.StartDate AS DATETIME) > dr.DayStart + '15:00:00' THEN CAST(t.StartDate AS DATETIME) ELSE dr.DayStart + '15:00:00' END,
                CASE WHEN CAST(t.EndDate AS DATETIME) < dr.DayEnd + '19:00:00' THEN CAST(t.EndDate AS DATETIME) ELSE dr.DayEnd + '19:00:00' END
            ) 
            ELSE 0 
        END
    ) AS ValidDurationMinutes
FROM YourTable t
JOIN DateRange dr 
    ON dr.DateValue BETWEEN CAST(t.StartDate AS DATE) AND CAST(t.EndDate AS DATE)
CROSS APPLY (
    -- 生成当天的起始和结束时间（00:00:00 到次日00:00:00）
    SELECT 
        CAST(dr.DateValue AS DATETIME) AS DayStart,
        DATEADD(DAY, 1, CAST(dr.DateValue AS DATETIME)) AS DayEnd
) AS DayBounds
GROUP BY t.StartDate, t.EndDate
OPTION (MAXRECURSION 0); -- 处理跨多天的情况，取消递归次数限制

逻辑说明 #

1. 生成日期序列：用递归CTE生成StartDate到EndDate之间的所有日期，覆盖所有需要计算的天数。
2. 计算单天有效交集：对每个日期，分别计算两个有效时段与实际起止时间的重叠区间，仅统计正数的分钟差（避免无交集时出现负数）。
3. 累加总时长：将每天的有效分钟数求和，得到整个时间区间内的有效总时长。

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