MySQL中计算工单工作日处理时长的实现方法求助

核心逻辑拆解 #

要准确计算仅包含工作日09:00-13:00、14:00-18:00的工单处理时长，需分三步落地：

1. 修正时间边界：把创建/关闭时间调整到最近的有效工作时段内（比如周末时间直接跳转到工作日的工作时段，非工作时段的时间调整到当天工作时段的起点/终点）
2. 计算完整工作日时长：统计创建和关闭时间之间的完整工作日数量，乘以每天的有效时长（8小时）
3. 计算首尾部分时长：单独计算创建当天和关闭当天的有效工作时长（如果创建和关闭不在同一天）

错误分析（针对你提供的ChatGPT语句） #

你之前的语句核心问题是周末时间处理逻辑错误：

• 周六（MySQL中DAYOFWEEK=7）应直接跳转到下周一09:00，而非加固定时长；周五晚于18点的创建时间，同样要跳转到下周一09:00
• 周日（DAYOFWEEK=1）的关闭时间应跳转到上周五18:00，而非错误的时间偏移

可运行的SQL代码（适配osTicket + MySQL） #

SELECT
    t.ticket_id,
    t.created,
    t.closed,
    -- 计算总有效工作时长（单位：小时，四舍五入）
    ROUND(
        (
            -- 1. 中间完整工作日的时长：每天8小时，转成秒
            DATEDIFF(DAY, adjusted_created_date, adjusted_closed_date) * 8 * 3600
            -- 2. 创建当天的有效时长（秒）
            + created_day_seconds
            -- 3. 关闭当天的有效时长（秒）
            + closed_day_seconds
        ) / 3600
    ) AS processing_time_hours
FROM (
    SELECT
        ticket_id,
        created,
        closed,
        -- 调整后的创建时间（确保在有效工作时段内）
        CASE
            -- 周日/周六：跳转到下周一09:00
            WHEN DAYOFWEEK(created) IN (1,7) THEN DATE_ADD(DATE(created), INTERVAL (9 + (9 - DAYOFWEEK(created))*24) HOUR)
            -- 工作日早于09:00：设为当天09:00
            WHEN TIME(created) < '09:00:00' THEN DATE_ADD(DATE(created), INTERVAL 9 HOUR)
            -- 工作日在13:00-14:00之间：设为当天14:00
            WHEN TIME(created) BETWEEN '13:00:00' AND '13:59:59' THEN DATE_ADD(DATE(created), INTERVAL 14 HOUR)
            -- 工作日晚于18:00：设为当天18:00
            WHEN TIME(created) > '18:00:00' THEN DATE_ADD(DATE(created), INTERVAL 18 HOUR)
            -- 其他情况：保持原时间
            ELSE created
        END AS adjusted_created,
        -- 调整后的关闭时间（确保在有效工作时段内）
        CASE
            -- 周日：跳转到上周五18:00
            WHEN DAYOFWEEK(closed) = 1 THEN DATE_SUB(DATE(closed), INTERVAL 24 + 6 HOUR)
            -- 周六：跳转到上周五18:00
            WHEN DAYOFWEEK(closed) = 7 THEN DATE_SUB(DATE(closed), INTERVAL 18 HOUR)
            -- 工作日早于09:00：设为当天09:00
            WHEN TIME(closed) < '09:00:00' THEN DATE_ADD(DATE(closed), INTERVAL 9 HOUR)
            -- 工作日在13:00-14:00之间：设为当天13:00
            WHEN TIME(closed) BETWEEN '13:00:00' AND '13:59:59' THEN DATE_ADD(DATE(closed), INTERVAL 13 HOUR)
            -- 工作日晚于18:00：设为当天18:00
            WHEN TIME(closed) > '18:00:00' THEN DATE_ADD(DATE(closed), INTERVAL 18 HOUR)
            -- 其他情况：保持原时间
            ELSE closed
        END AS adjusted_closed,
        -- 提取调整后的创建日期（用于计算完整工作日）
        DATE(CASE
            WHEN DAYOFWEEK(created) IN (1,7) THEN DATE_ADD(DATE(created), INTERVAL (9 - DAYOFWEEK(created)) DAY)
            ELSE DATE(created)
        END) AS adjusted_created_date,
        -- 提取调整后的关闭日期（用于计算完整工作日）
        DATE(CASE
            WHEN DAYOFWEEK(closed) IN (1,7) THEN DATE_SUB(DATE(closed), INTERVAL (DAYOFWEEK(closed)-5) DAY)
            ELSE DATE(closed)
        END) AS adjusted_closed_date,
        -- 计算创建当天的有效时长（秒）
        CASE
            -- 创建和关闭在同一天：直接算调整后时间差
            WHEN DATE(adjusted_created) = DATE(adjusted_closed) THEN TIMESTAMPDIFF(SECOND, adjusted_created, adjusted_closed)
            ELSE
                -- 计算从调整后创建时间到当天18:00的时长，扣除13:00-14:00的1小时（如果创建时间在13点前）
                TIMESTAMPDIFF(SECOND, adjusted_created, DATE_ADD(DATE(adjusted_created), INTERVAL 18 HOUR))
                - CASE WHEN TIME(adjusted_created) < '13:00:00' THEN 3600 ELSE 0 END
        END AS created_day_seconds,
        -- 计算关闭当天的有效时长（秒）
        CASE
            -- 创建和关闭在同一天：设为0（已在创建当天算过）
            WHEN DATE(adjusted_created) = DATE(adjusted_closed) THEN 0
            ELSE
                -- 计算从当天09:00到调整后关闭时间的时长，扣除13:00-14:00的1小时（如果关闭时间在14点后）
                TIMESTAMPDIFF(SECOND, DATE_ADD(DATE(adjusted_closed), INTERVAL 9 HOUR), adjusted_closed)
                - CASE WHEN TIME(adjusted_closed) > '14:00:00' THEN 3600 ELSE 0 END
        END AS closed_day_seconds
    FROM ost_ticket
    -- 仅统计已关闭的工单
    WHERE closed IS NOT NULL
) t;

关键细节说明 #

1. DAYOFWEEK规则：MySQL中DAYOFWEEK()返回值1=周日，2=周一，7=周六；如果用PostgreSQL等其他数据库，需替换为EXTRACT(DOW FROM created)（0=周日，6=周六），并对应调整逻辑。
2. 午休时间扣除：代码自动排除了13:00-14:00的非工作时段，不需要的话可删除对应的- CASE ...部分。
3. 节假日扩展：若需排除法定节假日，可新增holidays表，计算完整工作日时用DATEDIFF结果减去节假日数量，比如：

   (DATEDIFF(DAY, adjusted_created_date, adjusted_closed_date) - (SELECT COUNT(*) FROM holidays WHERE holiday_date BETWEEN adjusted_created_date AND adjusted_closed_date)) * 8 * 3600
   

内容的提问来源于stack exchange，提问作者Francesco Della Morte