MySQL如何判断给定日期是否匹配start字段对应的星期几？

表结构说明 #

presence表存储用户在岗规则，字段定义如下：

• id: 记录主键，示例值1
• uid: 用户ID，示例值14
• start: 在岗起始时间，示例值2021-11-01 00:00:00
• end: 在岗结束时间，示例值2021-12-31 00:00:00，为null时表示无结束时间限制
• repetitive: 重复标记，1表示按星期重复（与start同星期几的日期均有效），0表示仅start到end的时间段有效

查询规则 #

根据给定日期查询符合条件的在岗记录，需满足以下规则：

1. 给定日期必须大于等于start的日期部分
2. 若end不为null，给定日期必须小于等于end的日期部分；若end为null则跳过结束时间校验
3. 若repetitive为1，额外要求给定日期的星期几和start的星期几完全一致
4. 若repetitive为0，仅需满足上述时间区间校验即可

实现SQL #

将SQL中的@target_date替换为你需要查询的具体日期即可：

SELECT *
FROM presences
WHERE 
    DATE(@target_date) >= DATE(`start`)
    AND (DATE(`end`) IS NULL OR DATE(@target_date) <= DATE(`end`))
    AND (
        `repetitive` = 0 
        OR DAYOFWEEK(DATE(@target_date)) = DAYOFWEEK(DATE(`start`))
    );

逻辑说明 #

• 所有日期比较统一先转换为DATE类型，避免字段带时分秒导致的比较误差
• 使用DAYOFWEEK函数计算星期几，保证目标日期和start日期的计算逻辑一致：如果你的业务需要适配其他星期计数规则，可替换为WEEKDAY等其他MySQL日期函数，只需保证左右两边调用的函数一致即可

测试验证 #

以示例记录（repetitive=1，start为2021-11-01周一，end为2021-12-31）为例：

• 查询日期为2021-11-15（周一）：在时间区间内且星期匹配，正常返回记录
• 查询日期为2021-11-16（周二）：星期不匹配，无返回结果
• 若将该记录的end设为null，查询2022-01-10（周一）：满足时间下限且星期匹配，正常返回记录

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