如何校验startprocesstime与endprocesstime是否在员工排班时段范围内？

核心问题分析 #

这里的关键挑战是跨天排班（比如示例中的16:00到次日01:00），常规的时间区间比较逻辑（start <= time <= end）会失效，因为EndShift的时间值小于StartShift。

通用校验逻辑 #

我们需要区分两种排班场景：

• 非跨天排班（比如9:00到18:00）：直接判断处理时间的起始和结束都落在[StartShift, EndShift]区间内
• 跨天排班（比如16:00到次日01:00）：处理时间要么落在[StartShift, 当天23:59:59]，要么落在[次日00:00:00, EndShift]

SQL实现示例（以MySQL为例） #

假设两张表分别为process_records（处理记录表）和employee_shifts（员工排班表），且通过EmployeeID关联：

SELECT
    pr.EmployeeID,
    pr.startprocesstime,
    pr.endprocesstime,
    es.StartShift,
    es.EndShift,
    -- 校验处理时间段是否完全在排班时段内
    CASE
        -- 非跨天排班：StartShift <= EndShift
        WHEN es.StartShift <= es.EndShift THEN
            (pr.startprocesstime >= CONCAT(DATE(pr.startprocesstime), ' ', es.StartShift))
            AND (pr.endprocesstime <= CONCAT(DATE(pr.endprocesstime), ' ', es.EndShift))
        -- 跨天排班：StartShift > EndShift
        ELSE
            (
                -- 处理时间完全在当天排班后半段
                pr.startprocesstime >= CONCAT(DATE(pr.startprocesstime), ' ', es.StartShift)
                AND pr.endprocesstime <= CONCAT(DATE(pr.startprocesstime), ' 23:59:59')
            )
            OR (
                -- 处理时间完全在次日凌晨排班段
                pr.startprocesstime >= CONCAT(DATE(pr.endprocesstime), ' 00:00:00')
                AND pr.endprocesstime <= CONCAT(DATE(pr.endprocesstime), ' ', es.EndShift)
            )
            OR (
                -- 处理时间跨天且完全覆盖在排班区间内
                pr.startprocesstime >= CONCAT(DATE(pr.startprocesstime), ' ', es.StartShift)
                AND pr.endprocesstime <= CONCAT(DATE(pr.endprocesstime) + INTERVAL 1 DAY, ' ', es.EndShift)
            )
    END AS is_within_shift
FROM
    process_records pr
JOIN
    employee_shifts es ON pr.EmployeeID = es.EmployeeID;

针对示例场景的验证 #

场景1：处理时间为2023-11-18 04:30:00 至 2023-11-18 04:31:00 #

员工排班为16:00（4pm）至次日01:00，该处理时间属于11月18日凌晨，不在11月17日16:00 - 11月18日01:00的排班区间内，因此is_within_shift返回FALSE。

场景2：处理时间为2023-11-19 00:30:00 至 2023-11-19 00:31:00 #

该时间段属于11月18日16:00 - 11月19日01:00的排班区间，因此is_within_shift返回TRUE。

注意事项 #

• 确保所有时间字段使用DATETIME类型存储，避免字符串格式不一致导致的比较错误
• 若排班表的StartShift和EndShift仅存储时间部分（如16:00:00），需与处理时间的日期部分拼接后再比较
• 逻辑支持处理时间跨天的情况（比如从2023-11-18 23:50到2023-11-19 00:10），只要全程落在排班区间内即可正确识别

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