如何在SQL Server的TSQL中将起止时间拆分为指定间隔？

实现思路 #

1. 时间取整处理
   • 开始时间：向上取整至最近的整点或30分钟节点
   • 结束时间：向下取整至最近的整点或30分钟节点
2. 生成时间间隔序列
   • 使用递归CTE从取整后的开始时间出发，每次累加指定间隔，直到达到结束时间

完整TSQL代码 #

DECLARE 
    @StartTime DATETIME = '7/27/2022 7:55 AM',
    @EndTime DATETIME = '7/27/2022 5:07 PM',
    @Interval INT = 30; -- 间隔分钟数

-- 1. 对起止时间进行取整处理
DECLARE 
    @RoundedStart DATETIME,
    @RoundedEnd DATETIME;

-- 开始时间向上取整到整点/半点
SET @RoundedStart = DATEADD(MINUTE, 
    ((DATEDIFF(MINUTE, 0, @StartTime) + @Interval - 1) / @Interval) * @Interval, 
    0);

-- 结束时间向下取整到整点/半点
SET @RoundedEnd = DATEADD(MINUTE, 
    (DATEDIFF(MINUTE, 0, @EndTime) / @Interval) * @Interval, 
    0);

-- 2. 递归CTE生成时间间隔序列
WITH TimeIntervals AS (
    SELECT 
        @RoundedStart AS IntervalStart,
        DATEADD(MINUTE, @Interval, @RoundedStart) AS IntervalEnd
    UNION ALL
    SELECT 
        IntervalEnd,
        DATEADD(MINUTE, @Interval, IntervalEnd)
    FROM TimeIntervals
    WHERE IntervalEnd < @RoundedEnd
)
-- 格式化输出结果
SELECT 
    FORMAT(IntervalStart, 'h:mm tt') + ' - ' + FORMAT(IntervalEnd, 'h:mm tt') AS TimeInterval
FROM TimeIntervals
OPTION (MAXRECURSION 0); -- 解除递归次数限制（当间隔小时数较多时需要）

代码说明 #

• 时间取整部分：
  • 利用DATEDIFF(MINUTE, 0, @StartTime)计算从SQL Server基准时间（1900-01-01 00:00:00）到目标时间的分钟数
  • 向上取整通过(分钟数 + 间隔-1)/间隔的整数除法实现，再乘以间隔数还原为分钟，最后用DATEADD转换为datetime类型
  • 向下取整直接用分钟数/间隔的整数除法，再还原为datetime
• 递归CTE部分：
  • 初始成员定义第一个时间区间
  • 递归成员不断累加间隔生成下一个区间，直到区间结束时间不小于取整后的结束时间
  • OPTION (MAXRECURSION 0)用于处理较长时间范围的情况，默认递归次数限制为100

测试结果 #

执行上述代码后，输出将与示例一致：

8:00 AM - 8:30 AM
8:30 AM - 9:00 AM
...
4:30 PM - 5:00 PM

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