SQL Server 2014窗口查询：拆分跨多30分钟间隔的记录为单行

这个需求在日志分析、工时统计这类场景里特别实用，我来给你分享几种主流数据库下的实现方案，核心思路都是先生成覆盖目标时间范围的30分钟间隔序列，再计算每个间隔内的实际有效时长：

假设你的数据存在time_records表中，包含id、start_time、end_time字段。我们可以用递归CTE生成所需的时间间隔：

WITH RECURSIVE time_intervals AS (
    -- 初始行：把原记录的开始时间向下取整到最近的30分钟节点
    SELECT 
        id,
        start_time,
        end_time,
        DATE_FORMAT(start_time, '%Y-%m-%d %H:00:00') + INTERVAL (FLOOR(MINUTE(start_time)/30)*30) MINUTE AS interval_start
    FROM time_records
    WHERE id = 1 -- 替换成你要查询的记录ID，去掉WHERE可处理全表记录
    UNION ALL
    -- 递归生成后续的30分钟间隔，直到覆盖结束时间的向上取整节点
    SELECT 
        id,
        start_time,
        end_time,
        interval_start + INTERVAL 30 MINUTE
    FROM time_intervals
    WHERE interval_start + INTERVAL 30 MINUTE <= DATE_FORMAT(end_time, '%Y-%m-%d %H:00:00') + INTERVAL (CEIL(MINUTE(end_time)/30)*30) MINUTE
)
-- 计算每个间隔的实际有效起止时间和耗时
SELECT 
    id,
    interval_start,
    interval_start + INTERVAL 30 MINUTE AS interval_end,
    GREATEST(interval_start, start_time) AS actual_start, -- 取间隔开始和原记录开始的较大值
    LEAST(interval_start + INTERVAL 30 MINUTE, end_time) AS actual_end, -- 取间隔结束和原记录结束的较小值
    TIMESTAMPDIFF(SECOND, GREATEST(interval_start, start_time), LEAST(interval_start + INTERVAL 30 MINUTE, end_time)) AS duration_seconds
FROM time_intervals
ORDER BY interval_start;

PostgreSQL自带的generate_series函数可以更简洁地生成时间序列，省去递归步骤：

WITH target_record AS (
    -- 这里替换成你的目标记录，或者直接关联原表
    SELECT 
        '2024-01-01 02:01:37'::timestamp AS start_time,
        '2024-01-01 05:00:21'::timestamp AS end_time
)
SELECT 
    ts AS interval_start,
    ts + INTERVAL '30 minutes' AS interval_end,
    GREATEST(ts, start_time) AS actual_start,
    LEAST(ts + INTERVAL '30 minutes', end_time) AS actual_end,
    EXTRACT(EPOCH FROM (LEAST(ts + INTERVAL '30 minutes', end_time) - GREATEST(ts, start_time))) AS duration_seconds
FROM target_record,
     generate_series(
         -- 生成从原开始时间向下取整的30分钟节点
         date_trunc('hour', start_time) + INTERVAL '30 minutes' * FLOOR(date_part('minute', start_time)/30),
         -- 到原结束时间向上取整的30分钟节点
         date_trunc('hour', end_time) + INTERVAL '30 minutes' * CEIL(date_part('minute', end_time)/30),
         -- 步长30分钟
         INTERVAL '30 minutes'
     ) AS ts
ORDER BY ts;

如果是SQL Server，2022及以上版本可以用GENERATE_SERIES，旧版本则用递归CTE：

-- 递归CTE版本（兼容所有SQL Server版本）
WITH time_intervals AS (
    SELECT 
        start_time,
        end_time,
        -- 把开始时间向下取整到最近的30分钟节点
        DATEADD(MINUTE, DATEDIFF(MINUTE, 0, start_time)/30*30, 0) AS interval_start
    FROM (
        -- 替换成你的目标记录或原表
        SELECT CAST('2024-01-01 02:01:37' AS datetime) AS start_time,
               CAST('2024-01-01 05:00:21' AS datetime) AS end_time
    ) AS t
    UNION ALL
    SELECT 
        start_time,
        end_time,
        DATEADD(MINUTE, 30, interval_start)
    FROM time_intervals
    -- 递归直到覆盖结束时间的向上取整节点
    WHERE DATEADD(MINUTE, 30, interval_start) <= DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, 0, end_time)/30.0)*30, 0)
)
SELECT 
    interval_start,
    DATEADD(MINUTE, 30, interval_start) AS interval_end,
    GREATEST(interval_start, start_time) AS actual_start,
    LEAST(DATEADD(MINUTE, 30, interval_start), end_time) AS actual_end,
    DATEDIFF(SECOND, GREATEST(interval_start, start_time), LEAST(DATEADD(MINUTE, 30, interval_start), end_time)) AS duration_seconds
FROM time_intervals
ORDER BY interval_start
OPTION (MAXRECURSION 0); -- 如果间隔数量超过100，需要开启这个选项

补充说明 #

• 如果要处理表中所有记录，只需要调整CTE的初始查询部分，去掉WHERE条件直接关联原表即可。
• actual_start和actual_end是该30分钟间隔内实际有记录的时间段，duration_seconds就是这个间隔内的耗时，你可以根据需求转换成分钟、小时等单位。
• 不同数据库的时间函数略有差异，但核心逻辑完全一致：先生成覆盖目标范围的30分钟间隔序列，再计算每个间隔的有效时间区间。

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