在SQL Server中拆分两日期时间间的分段记录（无需自定义函数）

核心思路 #

先通过CTE合并同一ID的起止时间，再生成覆盖该时间段的所有日期，最后对每个日期计算当天的实际起止时间及间隔，全程无需自定义函数。

通用实现逻辑（分数据库示例） #

1. PostgreSQL版本

WITH time_ranges AS (
    -- 合并同一ID的起止时间（你已实现的部分）
    SELECT 
        ID,
        MAX(CASE WHEN Action_c = 1 THEN Date_Time END) AS start_dt,
        MAX(CASE WHEN Action_c = 2 THEN Date_Time END) AS end_dt
    FROM main
    WHERE ID = 32221
    GROUP BY ID
),
date_series AS (
    -- 生成起止时间范围内的所有日期
    SELECT 
        tr.ID,
        tr.start_dt,
        tr.end_dt,
        generate_series(
            DATE_TRUNC('day', tr.start_dt),
            DATE_TRUNC('day', tr.end_dt),
            INTERVAL '1 day'
        )::DATE AS split_date
    FROM time_ranges tr
)
-- 计算每日分段的起止时间和间隔
SELECT 
    ID,
    split_date,
    GREATEST(start_dt, split_date::TIMESTAMP) AS daily_start,
    LEAST(end_dt, (split_date + INTERVAL '1 day')::TIMESTAMP) AS daily_end,
    -- 计算间隔（单位：小时，可按需改为分钟/秒）
    EXTRACT(EPOCH FROM (LEAST(end_dt, (split_date + INTERVAL '1 day')::TIMESTAMP) - GREATEST(start_dt, split_date::TIMESTAMP))) / 3600 AS duration_hours
FROM date_series
-- 过滤无有效时长的记录
WHERE GREATEST(start_dt, split_date::TIMESTAMP) < LEAST(end_dt, (split_date + INTERVAL '1 day')::TIMESTAMP);

2. MySQL版本

WITH RECURSIVE time_ranges AS (
    -- 合并同一ID的起止时间
    SELECT 
        ID,
        MAX(CASE WHEN Action_c = 1 THEN Date_Time END) AS start_dt,
        MAX(CASE WHEN Action_c = 2 THEN Date_Time END) AS end_dt
    FROM main
    WHERE ID = 32221
    GROUP BY ID
),
date_series AS (
    -- 递归生成日期序列
    SELECT 
        ID,
        start_dt,
        end_dt,
        DATE(start_dt) AS split_date
    FROM time_ranges
    UNION ALL
    SELECT 
        ID,
        start_dt,
        end_dt,
        DATE_ADD(split_date, INTERVAL 1 DAY)
    FROM date_series
    WHERE split_date < DATE(end_dt)
)
-- 计算每日分段的起止时间和间隔
SELECT 
    ID,
    split_date,
    GREATEST(start_dt, CONCAT(split_date, ' 00:00:00')) AS daily_start,
    LEAST(end_dt, CONCAT(DATE_ADD(split_date, INTERVAL 1 DAY), ' 00:00:00')) AS daily_end,
    -- 计算间隔（单位：小时）
    TIMESTAMPDIFF(HOUR, GREATEST(start_dt, CONCAT(split_date, ' 00:00:00')), LEAST(end_dt, CONCAT(DATE_ADD(split_date, INTERVAL 1 DAY), ' 00:00:00'))) AS duration_hours
FROM date_series
WHERE GREATEST(start_dt, CONCAT(split_date, ' 00:00:00')) < LEAST(end_dt, CONCAT(DATE_ADD(split_date, INTERVAL 1 DAY), ' 00:00:00'));

3. SQL Server版本

WITH time_ranges AS (
    -- 合并同一ID的起止时间
    SELECT 
        ID,
        MAX(CASE WHEN Action_c = 1 THEN Date_Time END) AS start_dt,
        MAX(CASE WHEN Action_c = 2 THEN Date_Time END) AS end_dt
    FROM main
    WHERE ID = 32221
    GROUP BY ID
),
date_series AS (
    -- 递归生成日期序列
    SELECT 
        ID,
        start_dt,
        end_dt,
        CAST(start_dt AS DATE) AS split_date
    FROM time_ranges
    UNION ALL
    SELECT 
        ID,
        start_dt,
        end_dt,
        DATEADD(DAY, 1, split_date)
    FROM date_series
    WHERE split_date < CAST(end_dt AS DATE)
)
-- 计算每日分段的起止时间和间隔
SELECT 
    ID,
    split_date,
    GREATEST(start_dt, CAST(split_date AS DATETIME)) AS daily_start,
    LEAST(end_dt, DATEADD(DAY, 1, CAST(split_date AS DATETIME))) AS daily_end,
    -- 计算间隔（单位：小时）
    DATEDIFF(HOUR, GREATEST(start_dt, CAST(split_date AS DATETIME)), LEAST(end_dt, DATEADD(DAY, 1, CAST(split_date AS DATETIME)))) AS duration_hours
FROM date_series
WHERE GREATEST(start_dt, CAST(split_date AS DATETIME)) < LEAST(end_dt, DATEADD(DAY, 1, CAST(split_date AS DATETIME)))
OPTION (MAXRECURSION 0); -- 时间段超过100天时需添加此参数

关键说明 #

• time_ranges CTE：复用你已实现的合并逻辑，将同一ID的Start（Action_c=1）和End（Action_c=2）时间合并到一行。
• date_series CTE：生成覆盖起止时间的所有日期，确保每个自然日都被拆分到。
• 最终计算：通过GREATEST和LEAST函数确定当天的实际起止时间，避免跨天的时间溢出，同时过滤掉无有效时长的无效记录。

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