sql - 查询以检查开始日期和结束日期之间的日期范围(如果已存在于 postgres 的数据库中)

Question

我必须在两个日期范围之间分配机票预订，即从可用的开始日期和结束日期(之前未分配)。为此，如果在这些日期之间已经发生任何分配，我需要检查数据库

例如，我需要分配从2017/04/20到2017/04/25的机票预订。在插入这条记录之前，我需要检查是否有任何预订发生在开始日期2017/04/20之间和结束日期 2017/04/25

我试过下面的查询，但没有给出正确的结果

select "id" 
from "table" 
where "from_date" >= '2017-04-20' 
  and "to_date" > '2017-04-20' 
  and "from_date" < '2017-04-25' 
  and "to_date" <= '2017-04-25'

Answer 1

您的查询没有给出预期的结果，因为您只测试了 3 种(或 4 种)可能的重叠/相交方式中的一种:

existing date periods:  +--------------+      +-----+       +----+   +-----+
                            contains        contained by        touches
testing periods:            +------+       +------------+     +---------+

要测试所有可能的重叠方式，您可以使用 daterange的重叠运算符:&&

select count(*)
from   booking
where  daterange(from_date, to_date, '[]') && daterange('2017-04-20', '2017-04-25', '[]');

但是警告:仅使用这些类型的手动检查不会阻止在高并发情况下插入重叠的日期范围。有一个(小)窗口，在此测试之后和实际插入另一个并发语句之前插入冲突行。为了避免这种情况，您可以使用 exclusion constraints :

alter table booking
  add constraint exclude_overlapping_bookings
  exclude using gist (daterange(from_date, to_date, '[]') with &&);

注意:daterange 的重叠运算符 (&&) 的工作方式与 overlaps 运算符相同， @a_horse_with_no_name 提到。除了:

• 它支持排除约束
• 它支持使用索引(如果你添加了排除约束，索引会自动为你创建)
• 您可以微调如何包含范围的边界。默认为[)，意思是:下限是包含的，但上限是独占的。对于 overlaps，这是唯一支持的包含。
• overlaps 使用 timestamp with time zone 值。您的 date 值将转换为该日期，使用午夜作为时间和当前的 TimeZone 设置。这可能是您想要的，也可能不是。

http://rextester.com/NNWD52481