如何编写日期字段WHERE CLAUSE 筛选与指定月份重叠的缺勤记录

核心逻辑 #

两个时间段存在重叠的通用判定规则为：时间段1的开始时间 ≤ 时间段2的结束时间，且时间段1的结束时间 ≥ 时间段2的开始时间。
对应到你的业务场景：

• 时间段1为缺勤时段：开始时间date_from，结束时间date_to
• 时间段2为查询月份：开始时间为查询月第一天，结束时间为查询月最后一天

示例验证 #

以你给出的示例测试：

• 缺勤时段：2021年8月1日 ~ 2021年11月11日
• 查询月份：2021年10月，对应时段为2021年10月1日 ~ 2021年10月31日
  判定结果：2021-08-01 ≤ 2021-10-31 成立，且2021-11-11 ≥ 2021-10-01 成立，符合筛选条件，会返回该记录。

代码实现 #

以MySQL为例的参考写法，支持传入YYYY-MM格式的月份参数：

-- 定义查询参数，示例为查询2021年10月
SET @query_month = '2021-10';
-- 自动计算查询月的第一天和最后一天
SET @query_month_start = DATE_FORMAT(@query_month, '%Y-%m-01');
SET @query_month_end = LAST_DAY(@query_month_start);

-- 查询语句
SELECT * 
FROM 你的缺勤表名
WHERE 
  date_from <= @query_month_end
  AND date_to >= @query_month_start;

特殊场景兼容 #

如果业务中存在未结束的缺勤，date_to字段用NULL表示缺勤仍在进行中，可以调整条件适配：

WHERE 
  date_from <= @query_month_end
  AND (date_to >= @query_month_start OR date_to IS NULL)

覆盖场景说明 #

该条件可以覆盖所有重叠场景：

• 缺勤时段完全在查询月内
• 缺勤开始于查询月之前，结束于查询月内
• 缺勤开始于查询月内，结束于查询月之后
• 缺勤时段完全覆盖整个查询月

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