MySQL根据跨特定时间节点的日期区间返回对应值的实现方法

规则对齐 #

先明确计算逻辑，和给出的业务规则、示例完全匹配：

• 单个完整非工作时段为当日18:00 至 次日09:00，时长固定13小时
• 只有当时间区间完整覆盖整个非工作时段时，才累计对应时长
• 覆盖1个完整时段返回13，覆盖2个返回26，更多时段可自动按13小时/个累加

方案1：直接查询嵌入计算 #

不需要创建额外数据库对象，直接在查询语句中即可完成计算，适合临时统计场景：

SELECT 
    from_date,
    to_date,
    (
        SELECT COUNT(1)*13
        FROM (
            -- 生成起止日期范围内所有日期序列
            SELECT DATE_ADD(DATE(from_date), INTERVAL (tens.num + units.num) DAY) AS stat_date
            FROM 
                (SELECT 0 num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) tens,
                (SELECT 0 num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) units
            WHERE DATE_ADD(DATE(from_date), INTERVAL (tens.num + units.num) DAY) <= DATE(to_date)
        ) date_seq
        WHERE 
            -- 校验非工作时段起点（当日18:00）晚于等于业务开始时间
            DATE_ADD(stat_date, INTERVAL 18 HOUR) >= from_date
            -- 校验非工作时段终点（次日09:00）早于等于业务结束时间
            AND DATE_ADD(stat_date, INTERVAL 33 HOUR) <= to_date
    ) AS no_work
FROM your_business_table;

注：上面的日期序列生成逻辑最多支持跨度100天的时间区间计算，如果业务场景有更长的时间跨度，多叠一层数字辅助表扩展即可。

方案2：封装自定义函数复用 #

如果需要在多个查询、业务场景中重复使用这个计算逻辑，可以封装为MySQL自定义函数，调用更简洁：

-- 先修改分隔符，避免函数体内分号和默认分隔符冲突
DELIMITER //
CREATE FUNCTION calc_no_work_duration(from_date DATETIME, to_date DATETIME)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE total_no_work INT DEFAULT 0;
    DECLARE curr_period_start DATETIME;
    -- 初始化第一个待校验的非工作时段起点：起始日期当天18:00
    SET curr_period_start = DATE_ADD(DATE(from_date), INTERVAL 18 HOUR);

    -- 循环校验所有可能落在时间区间内的非工作时段
    WHILE curr_period_start < to_date DO
        -- 若当前非工作时段的终点（次日09:00）早于等于结束时间，说明被完整覆盖
        IF DATE_ADD(curr_period_start, INTERVAL 15 HOUR) <= to_date THEN
            SET total_no_work = total_no_work + 13;
        END IF;
        -- 偏移到下一天的非工作时段起点
        SET curr_period_start = DATE_ADD(curr_period_start, INTERVAL 1 DAY);
    END WHILE;

    RETURN total_no_work;
END //
-- 恢复默认分隔符
DELIMITER ;

函数调用方式非常简单，直接传入起止时间即可：

SELECT 
    from_date,
    to_date,
    calc_no_work_duration(from_date, to_date) AS no_work
FROM your_business_table;

结果验证 #

用给出的两组业务样例测试，返回结果完全符合预期：

1. 入参from_date = '2022-07-13 09:26'、to_date = '2022-07-14 17:56'，返回no_work = 13
2. 入参from_date = '2022-07-13 09:26'、to_date = '2022-07-15 17:56'，返回no_work = 26

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