MySQL 查询hotel_booking表中与指定时间段重叠的预订记录

基础信息 #

• 表名：hotel_booking
• 核心字段：
  • reference：预订编号
  • start_date：入住起始日期
  • numberOfDays：入住天数

需求说明 #

需要查询所有和指定时间段存在重叠的预订记录，而非仅入住开始时间落在指定区间的记录。
示例场景下指定查询时间段为2021-08-11至2021-08-12，预期返回符合要求的预订：JKL、ABC、DEF。

实现逻辑 #

判断两个日期区间重叠的通用规则为：预订入住开始时间 < 查询区间结束日期+1天，且预订退房时间 > 查询区间开始日期。

注：日期计算采用半开区间[起始日期, 结束日期)规则，可避免跨天边界判断错误：例如8月10日入住2天，实际入住周期为2021-08-10至2021-08-12（12日当天退房，不计入入住周期）。

通用SQL实现 #

SELECT reference
FROM hotel_booking
WHERE 
  DATE(start_date) < DATE_ADD('查询结束日期', INTERVAL 1 DAY)
  AND DATE_ADD(DATE(start_date), INTERVAL numberOfDays DAY) > '查询开始日期';

示例场景SQL（查询2021-08-11至2021-08-12重叠的预订） #

SELECT reference
FROM hotel_booking
WHERE 
  DATE(start_date) < DATE_ADD('2021-08-12', INTERVAL 1 DAY)
  AND DATE_ADD(DATE(start_date), INTERVAL numberOfDays DAY) > '2021-08-11';

不同数据库兼容说明 #

如果使用非MySQL类数据库，日期加法函数可对应调整：

• SQL Server：将DATE_ADD(单位, 增量, 日期)替换为DATEADD(day, 增量, 日期)
• Oracle：直接用日期加数字语法，例如DATE(start_date) + numberOfDays
• PostgreSQL：可使用DATE(start_date) + numberOfDays * INTERVAL '1 day'

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