如何计算跨日期DateTime区间分钟数并按对应日期归属统计

你的需求是将跨日期的时段拆分到对应日期统计分钟数，完全可以通过SQL实现。先指出你当前SQL的几个问题：

1. 列名错误：原表列是Start和End，你的SQL里写成了StartDate和EndDate
2. 逻辑不完整：仅计算了单段分钟数，没有拆分跨天记录生成多行结果
3. GROUP BY不合理：你需要保留每条记录的拆分明细，而非按员工和任务分组

正确实现思路 #

通过递归CTE生成每条记录覆盖的所有日期，然后针对每个日期计算对应的有效时段分钟数：

• 对于记录的起始日期：时段为Start到当日午夜（次日00:00:00）
• 对于记录的结束日期：时段为当日00:00:00到End
• 对于中间的日期（如果跨多天）：时段为全天（00:00:00到次日00:00:00），对应1440分钟

完整SQL代码（以SQL Server为例） #

WITH DateRanges AS (
    -- 初始CTE：处理每条原始记录，生成起始日期和结束日期
    SELECT 
        ID,
        Start,
        End,
        taskId,
        EmployeeId,
        CAST(Start AS DATE) AS CurrentDate,
        CAST(End AS DATE) AS EndDate
    FROM tbl
    UNION ALL
    -- 递归生成中间日期
    SELECT 
        ID,
        Start,
        End,
        taskId,
        EmployeeId,
        DATEADD(DAY, 1, CurrentDate) AS CurrentDate,
        EndDate
    FROM DateRanges
    WHERE CurrentDate < EndDate
)
SELECT
    Start,
    End,
    taskId,
    EmployeeId AS EmpId,
    -- 计算每个日期对应的分钟数
    DATEDIFF(MINUTE,
        -- 当日的开始时间：起始日期用原Start，其他日期用00:00:00
        CASE WHEN CurrentDate = CAST(Start AS DATE) THEN Start ELSE CAST(CurrentDate AS DATETIME) END,
        -- 当日的结束时间：结束日期用原End，其他日期用次日00:00:00
        CASE WHEN CurrentDate = CAST(End AS DATE) THEN End ELSE DATEADD(DAY, 1, CAST(CurrentDate AS DATETIME)) END
    ) AS minutes
FROM DateRanges
ORDER BY ID, CurrentDate;

执行结果验证 #

运行上述代码后，将得到你期望的结果：

Start                End           taskId   EmpId       minutes
2023-11-1 20:30:00  2023-11-1 23:30        2         1       180
2023-11-1 21:30:00  2023-11-1 23:30        2         2       120
2023-11-1 20:30:00  2023-11-2 01:30        2         3       210
2023-11-1 20:30:00  2023-11-2 01:30        2         3        90
2023-11-2 20:30:00  2023-11-2 23:30        2         1       180
2023-11-2 20:30:00  2023-11-2 23:30        2         2       180
2023-11-2 20:30:00  2023-11-3 00:30        2         3       210
2023-11-2 20:30:00  2023-11-3 00:30        2         3        30

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