SQL Server实现计算两日期22:00至次日6:00夜间时长的函数需求

实现思路 #

• 明确夜间规则：当日22:00:00到次日06:00:00为夜间，单段夜间总时长8小时
• 先修正输入参数顺序，保证开始时间早于结束时间
• 由于输入日期间隔不超过4天，遍历覆盖时间范围内的所有日期，逐段构造对应夜间区间
• 计算输入时间范围与每个夜间区间的交集时长，累加后保留6位小数即可

完整实现代码 #

CREATE FUNCTION dbo.CalcNightDuration
(
    @date1 DATETIME,
    @date2 DATETIME
)
RETURNS DECIMAL(18,6)
AS
BEGIN
    -- 修正参数顺序，避免输入顺序颠倒出错
    DECLARE @start DATETIME = IIF(@date1 < @date2, @date1, @date2)
    DECLARE @end DATETIME = IIF(@date1 > @date2, @date1, @date2)
    DECLARE @totalHours DECIMAL(18,6) = 0
    DECLARE @curDate DATE = CAST(@start AS DATE)
    DECLARE @curNightStart DATETIME, @curNightEnd DATETIME
    DECLARE @segStart DATETIME, @segEnd DATETIME

    WHILE @curDate <= CAST(@end AS DATE)
    BEGIN
        -- 构造当日对应夜间时段：当日22点到次日6点
        SET @curNightStart = DATEADD(HOUR, 22, CAST(@curDate AS DATETIME))
        SET @curNightEnd = DATEADD(HOUR, 30, CAST(@curDate AS DATETIME))
        -- 取两个时间段的交集
        SET @segStart = IIF(@start > @curNightStart, @start, @curNightStart)
        SET @segEnd = IIF(@end < @curNightEnd, @end, @curNightEnd)
        -- 存在有效交集则累加时长，按秒换算小时保证精度
        IF @segStart < @segEnd
        BEGIN
            SET @totalHours = @totalHours + DATEDIFF(SECOND, @segStart, @segEnd) / 3600.0
        END
        SET @curDate = DATEADD(DAY, 1, @curDate)
    END
    RETURN ROUND(@totalHours, 6)
END
GO

效果校验 #

执行测试语句即可验证需求示例：

-- 示例1测试，预期输出16.000000
SELECT dbo.CalcNightDuration('2021-06-01 05:00:00.000', '2021-06-03 05:00:00.000') AS 夜间时长_小时

-- 示例2测试，预期输出1.083333
SELECT dbo.CalcNightDuration('2021-06-22 04:55:00.000', '2021-06-22 16:45:00.000') AS 夜间时长_小时

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