如何用SQL计算跨多天的两个时间戳间PEAK时段总时长

核心思路 #

要计算date_reported到date_completed区间内的高峰（7:00-10:00）总时长，需拆分三个部分分别计算后求和：

• 起始日期当天的有效高峰时长
• 起始日与结束日之间完整天数的高峰总时长（每天固定3小时）
• 结束日期当天的有效高峰时长

SQL实现（以PostgreSQL为例） #

以下代码可直接用于计算单条或批量事务的高峰时长：

WITH transaction_dates AS (
    SELECT
        date_reported,
        date_completed,
        -- 提取起始日期的纯日期部分
        DATE(date_reported) AS start_date,
        -- 提取结束日期的纯日期部分
        DATE(date_completed) AS end_date,
        -- 将起始时间转换为小时数（含分钟，如8:30AM为8.5）
        EXTRACT(EPOCH FROM (date_reported - DATE(date_reported))) / 3600 AS start_hour,
        -- 将结束时间转换为小时数
        EXTRACT(EPOCH FROM (date_completed - DATE(date_completed))) / 3600 AS end_hour
    FROM your_transaction_table
)
SELECT
    date_reported,
    date_completed,
    -- 总高峰时长（单位：小时）
    -- 同一天的情况：计算当天高峰时段与事务时段的重叠时长
    GREATEST(0, LEAST(10, end_hour) - GREATEST(7, start_hour)) * CASE WHEN start_date = end_date THEN 1 ELSE 0 END
    -- 中间完整天数的高峰时长
    + GREATEST(0, (end_date - start_date - 1) * 3)
    -- 跨天情况：结束日的高峰时长
    + CASE WHEN start_date != end_date THEN GREATEST(0, LEAST(10, end_hour) - 7) ELSE 0 END
    -- 跨天情况：起始日的高峰时长
    + CASE WHEN start_date != end_date THEN GREATEST(0, 10 - GREATEST(7, start_hour)) ELSE 0 END
    AS total_peak_hours
FROM transaction_dates;

代码解释 #

1. CTE预处理：拆分事务的日期和时间部分，简化后续计算：

   • start_date/end_date：提取上报、完成时间的纯日期
   • start_hour/end_hour：将时间转换为带小数的小时数，精准计算分钟级重叠

2. 分场景计算：

   • 同一天事务：直接取事务时段与7-10点的重叠区间，负数则计0
   • 跨天事务：分别计算起始日剩余高峰时长、中间完整天数的固定3小时/天、结束日的高峰时长，三者求和

示例验证 #

针对示例数据：date_reported='2023-08-16 08:00:00'，date_completed='2023-08-19 14:00:00'

• 起始日高峰时长：10 - 8 = 2小时
• 中间完整天数：2023-08-17、2023-08-18，共2天，2×3=6小时
• 结束日高峰时长：10 -7 =3小时
• 总时长：2+6+3=11小时，代码运行结果将返回11小时

适配MySQL的调整 #

MySQL中提取时间部分的方式不同，可将CTE部分替换为：

WITH transaction_dates AS (
    SELECT
        date_reported,
        date_completed,
        DATE(date_reported) AS start_date,
        DATE(date_completed) AS end_date,
        -- 转换为带小数的小时数
        HOUR(date_reported) + MINUTE(date_reported)/60 AS start_hour,
        HOUR(date_completed) + MINUTE(date_completed)/60 AS end_hour
    FROM your_transaction_table
)

后续计算逻辑保持不变即可。

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