使用MYSQL选择两个日期之间的行？

Question

我遇到了一些MYSQL方面的小问题。

我需要检查一个假日房间是否已经预订了一段时间。

我的数据库使用DATETIME字段，并且设置如下：

数据库图像链接，因为我的声望很低

我有一个像这样的查询：

SELECT * FROM `caravanavail1` 
WHERE (`checkIn` BETWEEN '2014-01-07 14:00:00' AND '2014-01-08 10:00:00') 
OR (`checkOut` BETWEEN '2014-01-07 14:00:00' AND '2014-01-08 10:00:00')

我将始终传入两个日期，但有时这些日期可能以多种方式冲突：

1. 入住时间可能在另一个假期的中间，而退房时间可能不在。
2. 退房时间可能在另一个假期的中间，而入住时间可能不在。
3. 它们可能与另一个假期的日期完全相同。
4. 我提供的入住和退房日期都可能在另一次预订之内。

我已经思考了几天，可以有人帮忙修复上面的SQL代码（我更喜欢单个查询，而不是两个），以便返回我所需的内容吗？

- Ashley Williams

---

你没有提及这背后的业务逻辑 - “假日房间”是什么意思，它有什么区别吗？在日期方面，什么定义为假日（周末？日期段？）？请澄清您想要做什么，以及实际的限制，以便我们能够提供帮助。 - Nick Andriopoulos

日期范围...该房间的管理员可以在该房间内放置任意长时间的假期。他们选择一个入住日期和一个退房日期。这就是我需要检查的所有信息。 - Ashley Williams

对于未来的代码：您不需要引用/反引号列和表名。它们只会增加视觉混乱，并且是您制造语法错误的另一种方式。您唯一需要它们的原因是如果您有一个列名是保留字，而使用保留字作为列名是一个可怕的想法，所以这是两个您可以同时避免的坏习惯。 - Andy Lester

我只是使用它们，因为这是我所学到的，但还是谢谢 :) - Ashley Williams

仍然存在问题:/ - Ashley Williams

Answer 1

        |________|      // date to book
    |______|            // condition 1
              |______|  // condition 2
          |____|        // condition 3 
    |________________|  // condition 4

如果这4个条件中的任何一个都成立，那么就意味着无法完成预订。

$in =  '2014-01-07 14:00:00';
$out = '2014-01-08 10:00:00';

SELECT * 
FROM `caravanavail1` 
WHERE 
  (`checkIn` <= '$in' AND `checkOut` <= '$out') // condition 1
  OR 
  (`checkIn` >= '$in' AND `checkOut` >= '$out')  // condition 2
  OR 
  (`checkIn` >= '$in' AND `checkOut` <= '$out') // condition 3
  OR 
  (`checkIn` <= '$in' AND `checkOut` >= '$out') // condition 4

如 Marc B 所说，这可以进一步简化： 所有条件的入住时间都在您想预订的退房时间之前，且所有条件的退房时间都在您想预订的入住时间之后。

进一步简化为：

SELECT * 
FROM `caravanavail1` 
WHERE 
  (`checkIn` < '$out' AND `checkOut` > '$in')

有时候，进行可视化操作会有所帮助 :)

- NDM

---

你提供的代码对我来说最有意义，但是你所有的 =< 应该改为 <=。然而，使用你提供的代码（加上我刚刚提到的修复），即使日期之间有间隔，我也无法预订任何日期了。 - Ashley Williams

修正了运算符。另外请注意，如果此查询返回结果，则在该时间段内您将无法预订，而不能反过来。 - NDM

我明白，如果您查看此图像：http://postimg.org/image/4dh2c72at/，您就会知道我的意思。我已经要求查询在我的数据库中尚未预订的一周，但仍然返回相反的结果。 - Ashley Williams

Answer 2

考虑下面这个图示。其中x和y是问题中的签入/签出时间，而p和q是数据库中的签入/签出时间。这两个日期范围有多种重叠方式：

     x   y
p  q               // complete non-overlap, no conflict
   p  q            // partial overlap, conflict
      p q          // partial overlap, conflict
       p  q        // partial overlap, conflict
           p   q   // complete non overlap, no conflict

如果您绘制逻辑图，您会发现

 if (q >= x) && (p <= y) {
       ... there is a conflict
 }

确保你做好万全准备。

- Marc B

Answer 3

为了确定是否存在重叠范围，更容易的方法是想象不存在重叠的情况，然后反转条件。如果一个范围完全在另一个范围之前或之后，则两个范围不重叠。

在SQL中，这意味着退房日期在给定的入住日期之前，或者入住日期在给定的退房日期之后：

SELECT * FROM `caravanavail1` WHERE NOT (checkOut <= '$in' OR checkIn >= '$out')

您可以将其简化为：

SELECT * FROM `caravanavail1` WHERE checkOut > '$in' AND checkIn < '$out'

- Joni