SQL实现按自定义时间拆分跨时段事件记录行

需求说明 #

需要将跨越指定时间点（如午夜、早7:00、早6:30等）的事件拆分为两条独立记录，分别计算拆分前后的时段时长。要求用SQL实现，禁止使用Python，优先支持自定义拆分时间配置。

示例输入 #

StartTime                EndTime                  Duration     EventID
12-14-2022 1:46:00AM     12-14-2022 5:51:00AM     245          11122
12-14-2022 9:38:00PM     12-15-2022 12:06:00AM    148          11123
12-15-2022 5:22:00PM     12-16-2022 3:30:00AM     608          11124
12-16-2022 3:00:00AM     12-17-2022 4:00:00AM     1500         11125

示例输出 #

StartTime                EndTime                  Duration     EventID
12-14-2022 1:46:00AM     12-14-2022 5:51:00AM     245          11122
12-14-2022 9:38:00PM     12-15-2022 12:00:00AM    142          11123
12-15-2022 12:00:00AM    12-15-2022 12:06:00AM    6            11123
12-15-2022 5:22:00PM     12-16-2022 12:00:00AM    398          11124
12-16-2022 12:00:00AM    12-16-2022 3:30:00AM     210          11124
12-16-2022 3:00:00AM     12-17-2022 12:00:00AM    1260         11125
12-17-2022 12:00:00AM    12-17-2022 4:00:00AM     240          11125

SQL实现方案 #

1. 午夜（00:00:00）拆分版本 #

假设事件表名为event_data，字段与示例一致。以下SQL通过UNION ALL将跨午夜的事件拆分为两条记录，同时保留未跨拆分点的原记录：

-- 保留未跨午夜的原记录
SELECT 
    StartTime,
    EndTime,
    Duration,
    EventID
FROM event_data
WHERE CAST(StartTime AS DATE) = CAST(EndTime AS DATE)

UNION ALL

-- 拆分跨午夜事件的第一部分（从开始时间到当日午夜）
SELECT 
    StartTime,
    DATEADD(DAY, DATEDIFF(DAY, 0, EndTime), 0) AS EndTime,
    DATEDIFF(MINUTE, StartTime, DATEADD(DAY, DATEDIFF(DAY, 0, EndTime), 0)) AS Duration,
    EventID
FROM event_data
WHERE CAST(StartTime AS DATE) < CAST(EndTime AS DATE)

UNION ALL

-- 拆分跨午夜事件的第二部分（从午夜到结束时间）
SELECT 
    DATEADD(DAY, DATEDIFF(DAY, 0, EndTime), 0) AS StartTime,
    EndTime,
    DATEDIFF(MINUTE, DATEADD(DAY, DATEDIFF(DAY, 0, EndTime), 0), EndTime) AS Duration,
    EventID
FROM event_data
WHERE CAST(StartTime AS DATE) < CAST(EndTime AS DATE)
ORDER BY EventID, StartTime;

2. 自定义拆分时间版本（如早7:00 AM） #

如果需要将拆分点改为自定义时间（比如每日7:00 AM），只需调整拆分时间的计算逻辑，将午夜替换为指定时分：

-- 定义自定义拆分时间（每日7:00 AM）
DECLARE @split_time TIME = '07:00:00';

-- 保留未跨拆分点的原记录
SELECT 
    StartTime,
    EndTime,
    Duration,
    EventID
FROM event_data
WHERE 
    -- 事件完全在拆分点同侧（同一天的拆分点前/后）
    (CAST(StartTime AS DATE) = CAST(EndTime AS DATE) 
     AND CAST(StartTime AS TIME) >= @split_time 
     AND CAST(EndTime AS TIME) >= @split_time)
    OR 
    (CAST(StartTime AS DATE) = CAST(EndTime AS DATE) 
     AND CAST(StartTime AS TIME) < @split_time 
     AND CAST(EndTime AS TIME) < @split_time)

UNION ALL

-- 拆分跨拆分点事件的第一部分（从开始时间到当日拆分点）
SELECT 
    StartTime,
    CAST(CAST(StartTime AS DATE) AS DATETIME) + CAST(@split_time AS DATETIME) AS EndTime,
    DATEDIFF(MINUTE, StartTime, CAST(CAST(StartTime AS DATE) AS DATETIME) + CAST(@split_time AS DATETIME)) AS Duration,
    EventID
FROM event_data
WHERE 
    -- 事件从拆分点前跨到当日拆分点后
    CAST(StartTime AS DATE) = CAST(EndTime AS DATE) 
    AND CAST(StartTime AS TIME) < @split_time 
    AND CAST(EndTime AS TIME) >= @split_time

UNION ALL

-- 拆分跨日期+拆分点事件的第一部分（从开始时间到当日拆分点）
SELECT 
    StartTime,
    CAST(CAST(StartTime AS DATE) AS DATETIME) + CAST(@split_time AS DATETIME) AS EndTime,
    DATEDIFF(MINUTE, StartTime, CAST(CAST(StartTime AS DATE) AS DATETIME) + CAST(@split_time AS DATETIME)) AS Duration,
    EventID
FROM event_data
WHERE 
    -- 事件跨日期，且开始时间在拆分点后
    CAST(StartTime AS DATE) < CAST(EndTime AS DATE) 
    AND CAST(StartTime AS TIME) >= @split_time

UNION ALL

-- 拆分跨日期+拆分点事件的中间部分（从次日拆分点到当日午夜）
SELECT 
    CAST(CAST(EndTime AS DATE) AS DATETIME) + CAST(@split_time AS DATETIME) AS StartTime,
    DATEADD(DAY, DATEDIFF(DAY, 0, EndTime), 0) AS EndTime,
    DATEDIFF(MINUTE, CAST(CAST(EndTime AS DATE) AS DATETIME) + CAST(@split_time AS DATETIME), DATEADD(DAY, DATEDIFF(DAY, 0, EndTime), 0)) AS Duration,
    EventID
FROM event_data
WHERE 
    CAST(StartTime AS DATE) < CAST(EndTime AS DATE) 
    AND CAST(EndTime AS TIME) < @split_time

UNION ALL

-- 拆分跨拆分点事件的第二部分（从拆分点到结束时间）
SELECT 
    CAST(CAST(StartTime AS DATE) AS DATETIME) + CAST(@split_time AS DATETIME) AS StartTime,
    EndTime,
    DATEDIFF(MINUTE, CAST(CAST(StartTime AS DATE) AS DATETIME) + CAST(@split_time AS DATETIME), EndTime) AS Duration,
    EventID
FROM event_data
WHERE 
    -- 事件从拆分点前跨到当日拆分点后
    CAST(StartTime AS DATE) = CAST(EndTime AS DATE) 
    AND CAST(StartTime AS TIME) < @split_time 
    AND CAST(EndTime AS TIME) >= @split_time

UNION ALL

-- 拆分跨日期+拆分点事件的第二部分（从午夜到结束时间，结束时间在拆分点前）
SELECT 
    DATEADD(DAY, DATEDIFF(DAY, 0, EndTime), 0) AS StartTime,
    EndTime,
    DATEDIFF(MINUTE, DATEADD(DAY, DATEDIFF(DAY, 0, EndTime), 0), EndTime) AS Duration,
    EventID
FROM event_data
WHERE 
    CAST(StartTime AS DATE) < CAST(EndTime AS DATE) 
    AND CAST(EndTime AS TIME) < @split_time

UNION ALL

-- 拆分跨日期+拆分点事件的第二部分（从次日拆分点到结束时间，结束时间在拆分点后）
SELECT 
    CAST(CAST(EndTime AS DATE) AS DATETIME) + CAST(@split_time AS DATETIME) AS StartTime,
    EndTime,
    DATEDIFF(MINUTE, CAST(CAST(EndTime AS DATE) AS DATETIME) + CAST(@split_time AS DATETIME), EndTime) AS Duration,
    EventID
FROM event_data
WHERE 
    CAST(StartTime AS DATE) < CAST(EndTime AS DATE) 
    AND CAST(EndTime AS TIME) >= @split_time
ORDER BY EventID, StartTime;

逻辑说明 #

• 用UNION ALL组合三类记录：未拆分的原记录、拆分后的前半段、拆分后的后半段
• 通过日期类型转换判断事件是否跨日期，通过时间类型转换判断是否跨自定义拆分点
• 自定义拆分时间时，通过TIME类型变量指定拆分点，结合日期拼接出精确的拆分时刻
• 时长计算使用DATEDIFF(MINUTE, start, end)获取分钟数，与示例中Duration单位一致

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