MySQL排班系统跨午夜日期时间区间舍入实现方法问询

核心逻辑说明 #

针对跨午夜的排班时段，核心判断规则为：任意时间值的时间部分如果小于等于07:30:00，则将日期减1天，其余情况保留原日期，时间部分全程保持原值不变。

具体实现代码 #

假设你的排班表中存储原始时间的字段名为work_time，可直接通过SQL表达式计算归属时间：

1. 计算完整归属时间（保留时分秒） #

SELECT 
    IF(
        TIME(work_time) <= '07:30:00',
        work_time - INTERVAL 1 DAY,
        work_time
    ) AS schedule_belong_time
FROM 你的排班表名;

2. 仅计算归属日期（不需要时分秒） #

SELECT 
    DATE(
        IF(
            TIME(work_time) <= '07:30:00',
            work_time - INTERVAL 1 DAY,
            work_time
        )
    ) AS schedule_belong_date
FROM 你的排班表名;

规则验证 #

拿示例值测试完全符合需求：

• 输入2021-08-02 00:30:01，输出2021-08-01 00:30:01
• 输入2021-08-02 03:30:01，输出2021-08-01 03:30:01
• 输入2021-08-02 07:30:00，输出2021-08-01 07:30:00
• 输入2021-08-01 11:30:01，输出原值2021-08-01 11:30:01

性能优化方案 #

如果该逻辑查询频率很高，可在MySQL 5.7及以上版本添加存储生成列，避免每次查询重复计算，还可对该列加索引提升查询速度：

ALTER TABLE 你的排班表名 
ADD COLUMN schedule_belong_date DATE 
GENERATED ALWAYS AS (
    DATE(IF(TIME(work_time) <= '07:30:00', work_time - INTERVAL 1 DAY, work_time))
) STORED;

后续查询直接使用schedule_belong_date字段即可。

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