如何编写MySQL查询校验日期时间是否处于指定区间实现员工在岗判断

底层表结构 #

CREATE TABLE `presence` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `eid` int(10) unsigned DEFAULT NULL,
  `start` timestamp NULL DEFAULT NULL,
  `end` timestamp NULL DEFAULT NULL,
  `frequence` int(11) DEFAULT NULL,
  `fulltime` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

示例测试数据 #

INSERT INTO `presence` (`id`, `eid`, `start`, `end`, `frequence`, `fulltime`) VALUES (1, 14, "2021-11-29 10:00:00", "2021-12-13 18:00:00", NULL, NULL);
INSERT INTO `presence` (`id`, `eid`, `start`, `end`, `frequence`, `fulltime`) VALUES (2, 14, "2021-11-29 10:00:00", "2021-12-13 18:00:00", 1, NULL);
INSERT INTO `presence` (`id`, `eid`, `start`, `end`, `frequence`, `fulltime`) VALUES (3, 14, "2021-11-29 10:00:00", "2021-12-13 18:00:00", 1, 1);
INSERT INTO `presence` (`id`, `eid`, `start`, `end`, `frequence`, `fulltime`) VALUES (4, 14, "2021-11-29 10:00:00", "2021-12-13 18:00:00", NULL, 1);
INSERT INTO `presence` (`id`, `eid`, `start`, `end`, `frequence`, `fulltime`) VALUES (5, 14, "2021-11-29 10:00:00", NULL, 1, 1);

业务规则说明 #

• 行id#1：员工（eid:14）在2021-11-29到2021-12-13期间，每天10:00到18:00在岗
• 行id#2：员工（eid:14）在2021-11-29到2021-12-13期间，每周一10:00到18:00在岗，周一规则由start的星期属性+frequence=1定义
• 行id#3：员工（eid:14）在2021-11-29到2021-12-13期间，每周一全天在岗，fulltime=1表示忽略具体时间判断
• 行id#4：员工（eid:14）在2021-11-29到2021-12-13期间，每天全天在岗
• 行id#5：员工（eid:14）从2021-11-29起永久每周一全天在岗，end为NULL表示无结束时间

正确查询实现 #

核心修正说明 #

原查询存在3个核心错误：

1. 缺少指定员工ID的筛选条件
2. 结束时间判断逻辑写反，且未处理end IS NULL的永久生效场景
3. 未加入fulltime字段的判断逻辑，时间校验规则混乱

最终查询SQL #

SET @eid = 14;
SET @date = "2021-12-13 19:00:00";
SET @fullday = 0; -- 1=判断当日是否有在岗记录，0=判断指定时间是否在岗

SELECT COUNT(*) as is_present
FROM `presence`
WHERE `eid` = @eid
-- 日期范围匹配
AND DATE(@date) >= DATE(`start`)
AND (`end` IS NULL OR DATE(@date) <= DATE(`end`))
-- 重复频率匹配
AND (`frequence` IS NULL OR WEEKDAY(@date) = WEEKDAY(`start`))
-- 时间匹配：仅非单日查询、非全天在岗规则时需要校验具体时间
AND (
    @fullday = 1 
    OR `fulltime` = 1
    OR (TIME(@date) >= TIME(`start`) AND TIME(@date) <= TIME(`end`))
);

如果返回的is_present大于0，即代表员工在岗。

PHP函数封装 #

function employee_is_present(int $eid, string $date): bool
{
    // 此处可替换为项目实际的数据库连接实例
    $pdo = new PDO('mysql:host=127.0.0.1;dbname=test;charset=utf8', 'root', '数据库密码');
    
    // 判断是否为单日查询（入参格式为Y-m-d）
    $fullday = (strlen($date) === 10) ? 1 : 0;
    // 统一日期格式避免SQL处理异常
    if ($fullday) {
        $date .= ' 00:00:00';
    }
    
    $sql = "SELECT COUNT(*) as is_present
            FROM `presence`
            WHERE `eid` = ?
            AND DATE(?) >= DATE(`start`)
            AND (`end` IS NULL OR DATE(?) <= DATE(`end`))
            AND (`frequence` IS NULL OR WEEKDAY(?) = WEEKDAY(`start`))
            AND (
                ? = 1 
                OR `fulltime` = 1
                OR (TIME(?) >= TIME(`start`) AND TIME(?) <= TIME(`end`))
            )";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$eid, $date, $date, $date, $fullday, $date, $date]);
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    return $result['is_present'] > 0;
}

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