如何按天拆分计算两个DateTime列的跨天时长并生成每日记录？

要实现跨天时段的按天拆分，核心是先生成覆盖Start_Time到End_time的所有日期序列，再针对每个日期计算当天实际的起止时段，最后算出对应时长。以下是基于递归CTE的通用实现方案（适用于SQL Server、PostgreSQL、MySQL 8.0+等支持递归查询的数据库）：

示例SQL代码 #

假设你的主表名为main_table，字段为ID、Start_Time、End_time、DateDiff，执行以下查询即可得到拆分后的每日记录：

WITH date_range AS (
    -- 初始行：取目标记录的开始日期
    SELECT 
        ID,
        Start_Time,
        End_time,
        CAST(Start_Time AS DATE) AS current_date
    FROM main_table
    WHERE ID = 32221 -- 需处理全表可移除该条件
    UNION ALL
    -- 递归生成后续日期，直到超过结束日期
    SELECT 
        ID,
        Start_Time,
        End_time,
        DATEADD(DAY, 1, current_date) AS current_date
    FROM date_range
    WHERE current_date < CAST(End_time AS DATE)
)
SELECT 
    ID,
    -- 当天实际开始时间：取记录开始时间和当天0点的最大值
    CASE 
        WHEN current_date = CAST(Start_Time AS DATE) THEN Start_Time
        ELSE CAST(current_date AS DATETIME)
    END AS Daily_Start_Time,
    -- 当天实际结束时间：取记录结束时间和次日0点的最小值
    CASE 
        WHEN current_date = CAST(End_time AS DATE) THEN End_time
        ELSE DATEADD(DAY, 1, CAST(current_date AS DATETIME))
    END AS Daily_End_Time,
    -- 计算当天时长（分钟）
    DATEDIFF(MINUTE,
        CASE 
            WHEN current_date = CAST(Start_Time AS DATE) THEN Start_Time
            ELSE CAST(current_date AS DATETIME)
        END,
        CASE 
            WHEN current_date = CAST(End_time AS DATE) THEN End_time
            ELSE DATEADD(DAY, 1, CAST(current_date AS DATETIME))
        END
    ) AS Daily_DateDiff
FROM date_range
ORDER BY current_date;

执行结果说明 #

针对你提供的示例数据（ID=32221，Start_Time=2022-01-01 13:10:00，End_time=2022-01-03 13:10:00），查询会返回3条记录：

• 第一条：Daily_Start_Time=2022-01-01 13:10:00，Daily_End_Time=2022-01-02 00:00:00，Daily_DateDiff=650分钟
• 第二条：Daily_Start_Time=2022-01-02 00:00:00，Daily_End_Time=2022-01-03 00:00:00，Daily_DateDiff=1440分钟
• 第三条：Daily_Start_Time=2022-01-03 00:00:00，Daily_End_Time=2022-01-03 13:10:00，Daily_DateDiff=790分钟

三条记录的时长总和为650+1440+790=2880分钟，与原记录的总时长一致。

注意事项 #

• 若需处理全表数据，移除CTE初始查询中的WHERE ID = 32221条件即可。
• 不同数据库的日期函数存在差异：比如PostgreSQL需用DATE_TRUNC('day', Start_Time)替代CAST(Start_Time AS DATE)，DATEADD对应+ INTERVAL '1 day'，需根据使用的数据库调整语法。

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