MySQL查询：结合办公时长计算起始时间加指定分钟的结束时间

要实现这个需求，核心是得跳过非办公时段（下班后、周末），累计指定的办公分钟数后得到正确的结束时间。先明确咱们的规则：

• 每日办公时段：09:00 - 17:30（单日有效办公时长为510分钟）
• 目标：从起始时间开始，累计指定分钟数的办公时间，算出最终结束时间

分步逻辑拆解 #

1. 处理起始当天的剩余办公时间：如果起始时间在办公时段内，先算出当天还能办公多久；如果在下班后，直接跳到下一个工作日。
2. 判断是否需要跨天：如果要加的分钟数小于等于当天剩余办公时间，直接在起始时间上加对应分钟数就行；要是超过了，就扣除当天剩余的，再算需要多少个完整工作日。
3. 跳过非工作日：计算完整工作日的时候，要自动跳过周六和周日（如果需要支持节假日，还得额外加节假日表判断）。
4. 计算最终结束时间：把剩下的分钟数加到下一个工作日的上班时间上，得到结果。

实现代码（自定义MySQL函数） #

我写了一个可复用的自定义函数，把上面的逻辑都封装进去了：

DELIMITER //

CREATE FUNCTION calculate_work_end_time(start_time DATETIME, add_minutes INT)
RETURNS DATETIME
DETERMINISTIC
BEGIN
    -- 定义办公时间边界
    DECLARE work_start TIME DEFAULT '09:00:00';
    DECLARE work_end TIME DEFAULT '17:30:00';
    DECLARE daily_work_minutes INT DEFAULT TIMESTAMPDIFF(MINUTE, work_start, work_end); -- 单日办公时长510分钟
    
    DECLARE remaining_minutes INT DEFAULT add_minutes;
    DECLARE current_date DATE DEFAULT DATE(start_time);
    DECLARE current_time TIME DEFAULT TIME(start_time);
    DECLARE end_datetime DATETIME;
    
    -- 第一步：处理起始当天的剩余办公时间
    IF current_time BETWEEN work_start AND work_end THEN
        -- 算出当天还能办公多久
        SET remaining_minutes = remaining_minutes - TIMESTAMPDIFF(MINUTE, current_time, work_end);
        IF remaining_minutes <= 0 THEN
            -- 当天就能完成，直接返回结果
            SET end_datetime = DATE_ADD(start_time, INTERVAL add_minutes MINUTE);
            RETURN end_datetime;
        END IF;
    ELSEIF current_time > work_end THEN
        -- 起始时间在下班后，直接跳到下一天
        SET current_date = DATE_ADD(current_date, INTERVAL 1 DAY);
    END IF;
    
    -- 第二步：跳过周末，计算需要多少个完整工作日
    WHILE remaining_minutes > daily_work_minutes DO
        SET remaining_minutes = remaining_minutes - daily_work_minutes;
        SET current_date = DATE_ADD(current_date, INTERVAL 1 DAY);
        -- 跳过周六（7）和周日（1）
        WHILE DAYOFWEEK(current_date) IN (1,7) DO
            SET current_date = DATE_ADD(current_date, INTERVAL 1 DAY);
        END WHILE;
    END IF;
    
    -- 第三步：处理剩余分钟数，得到最终结束时间
    -- 确保当前日期是工作日
    WHILE DAYOFWEEK(current_date) IN (1,7) DO
        SET current_date = DATE_ADD(current_date, INTERVAL 1 DAY);
    END WHILE;
    SET end_datetime = STR_TO_DATE(CONCAT(current_date, ' ', work_start), '%Y-%m-%d %H:%i:%s');
    SET end_datetime = DATE_ADD(end_datetime, INTERVAL remaining_minutes MINUTE);
    
    RETURN end_datetime;
END //

DELIMITER ;

使用示例 #

针对你给出的例子：起始时间2017-01-02 16:52，添加300分钟办公时间，执行下面的查询：

SELECT calculate_work_end_time('2017-01-02 16:52', 300);

具体计算过程：

1. 起始时间当天剩余办公时间：17:30 - 16:52 = 38分钟，剩余需要的分钟数变为300 - 38 = 262分钟
2. 262分钟小于单日办公时长510分钟，不需要完整工作日
3. 跳到下一个工作日2017-01-03（周二，不是周末），从09:00开始加262分钟：09:00 + 262分钟 = 13:22
4. 最终结果：2017-01-03 13:22

注：如果你的示例预期结果是2017-01-03 12:22，大概率是办公时段的起始时间定义不同（比如是08:00），只需要修改函数里的work_start变量值就行。

扩展说明 #

• 如果需要支持节假日，你得先创建一个节假日表，然后在函数里加判断逻辑，自动跳过这些日期。
• 函数默认跳过周末，如果你的工作日规则不一样（比如单休），修改DAYOFWEEK的判断条件即可（DAYOFWEEK返回1是周日，2是周一，7是周六）。

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