如何编写SQL查询获取指定周内所有相关事件（含跨周重叠）

可以用时间段重叠判断逻辑编写单个SQL查询，覆盖你提到的所有事件场景。核心逻辑是：事件的开始时间早于目标周的结束边界，且事件的结束时间晚于目标周的开始边界。

核心查询语句 #

假设你的表名为events，字段start和end为timestamp类型，先定义目标周的两个边界：

• $week_start：目标周的第一天（如7月16日00:00:00，格式为'YYYY-MM-DD HH:MM:SS'）
• $week_end：目标周结束后的第一天（如7月23日00:00:00，即目标周最后一天的次日零点）

查询语句如下：

SELECT *
FROM events
WHERE start < '{$week_end}'
  AND end > '{$week_start}';

为什么这个逻辑能覆盖所有场景？ #

针对你列出的4种情况逐一验证：

1. 起止均在周内：事件的start≥$week_start且end≤$week_end前的最后一秒 → 满足start < $week_end且end > $week_start
2. 开始于周前、结束于周内：start < $week_start且end > $week_start → 满足start < $week_end（周前时间必然早于周结束边界）且end > $week_start
3. 开始于周内、结束于周后：start < $week_end且end > $week_end → 满足start < $week_end且end > $week_start（周后时间必然晚于周开始边界）
4. 起止均在周外但覆盖整周：start < $week_start且end > $week_end → 直接满足两个条件

关于存储格式的建议 #

你当前用timestamp存储完全没问题，不需要刻意更换。如果场景不需要精确到时分秒，换成date类型也可以，逻辑完全一致，只需把边界改为纯日期格式（如'2023-07-16'和'2023-07-23'）。

优化提示 #

• 给start和end字段添加联合索引，能大幅提升大表下的查询效率：

  CREATE INDEX idx_events_start_end ON events(start, end);
  

• 注意时区一致性：如果你的timestamp带时区，确保目标周的边界时区和字段时区一致，避免时间偏移导致的错误。

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