如何在MySQL中检查两个日期范围是否重叠？

Question

在 mysql 中，如何检查两个日期范围是否重叠？

我有以下代码：

注意：我们有 p.date_started <= p.date_finished，但dateA可以等于dateB或小于dateB或大于dateB。

代码：

    $query = "SELECT u.first_name, u.last_name, u.avatar_filename, p.id, p.user_id, p.address, p.topic, p.latitude, p.longitude, d.name AS department_name
              FROM user u
              JOIN placement p ON p.user_id=u.id
              JOIN department d ON d.id = u.department_id
              WHERE p.active=1 AND (('{$dateA}' BETWEEN p.date_started AND p.date_finished) OR 
                                    ('{$dateB}' BETWEEN p.date_started AND p.date_finished) OR
                                    (p.date_started BETWEEN '{$dateA}' AND '{$dateB}') OR
                                    (p.date_finished BETWEEN '{$dateA}' AND '{$dateB}'))";

有没有更好的方法做这件事？

谢谢。

- omega

---

如果你不知道A在B之前还是之后，也不知道A-B是否完全嵌入起始-结束或反之，则我认为这就是最简短和简单的方式。 - GolezTrol

嗯，时间是线性的，对吧？一个事件不能在开始之前就结束！ - Strawberry

我并没有说dateA和dateB是一个事件。它们只是两个可以以任何顺序出现的日期。 - omega

Answer 1

如果我们可以保证date_started、date_finished、$DateA和$DateB不是NULL，并且我们可以保证date_started不大于date_finished……

`s` represents `date_started`
`f` represents `date_finished`
`a` represents the smaller of `$DateA` and `$DateB`
`b` represents the larger of `$DateA` and `$DateB`

视觉上：

      s-----f       overlap
 -----+-----+-----  -------  
  a-b |     |        NO
  a---b     |        YES
  a-----b   |        YES
  a---------b        YES
  a-----------b      YES
      a---b |        YES
      a-----b        YES
      a-------b      YES
      | a-b |        YES
      | a---b        YES     
      | a-----b      YES     
      |     a-b      YES
      |     | a-b    NO

当范围之间没有“重叠”时，我们可以轻松地检测到：

( a > f OR b < s )

我们可以轻松地对此进行否定，以在存在“重叠”时返回“true”：

NOT ( a > f OR b < s )

将其转换为SQL：

NOT ( GREATEST('{$dateA}','{$dateB}') < p.date_started
      OR LEAST('{$dateA}','{$dateB}') > p.date_finished
    )

- spencer7593

---

NOT ( a > f OR b < s ) 等同于 NOT ( a > f) AND NOT (b < s )。这与 a <= f AND b >= s 相同。而且这个可以使用一个列的索引范围扫描。 - Paul Spiegel

@PaulSpiegel：表中另一列有一个等式谓词（p.user_id = u.id），因此为了获得索引范围扫描，我们需要一个复合索引，其中 date_started（或 date_ended）作为第二列，user_id 作为主导列。如果我们有这个，那么重写对 date_started / date_finished 列的谓词将允许进行范围扫描操作。 - spencer7593

它还要短5个字节 :-) - Paul Spiegel

这个答案还没有解决处理边缘情况的问题，当 date_started 等于被检查范围的结束日期时...那是否会有重叠呢？将一个 OR p.date_finished IS NULL 条件加入到 SQL 中是有意义的，只要 NULL 值代表无限未来的一个点。我们需要注意到这种假设，建议 SQL 构造时要谨慎。因为并不是每个 DATE 列中的 NULL 值都代表这一点。 - spencer7593

@HusseinAkar date_finished 的 NULL 值是一个“缺失条件”，需要修复（如您的答案所示），仅当 NULL 值表示覆盖整个未来时期时，我们才能纳入条件。但我们需要小心地将该特定含义归纳到具有 NULL 值的行中。它也可能代表不能重叠的时间段，或者代表一年或一小时的默认值。我没有深入处理所有边缘情况的细节。这个答案关注的是所提出的问题。 - spencer7593

Answer 2

您可以通过以下方式处理所有日期重叠情况，即使数据库中的截止日期可能为空：

SELECT * FROM `tableName` t
WHERE t.`startDate` <= $toDate
AND (t.`endDate` IS NULL OR t.`endDate` >= $startDate);

这将返回所有与新的开始/结束日期有任何重叠的记录。

- Hussein Akar

Answer 3

检查重叠的逻辑

A -> B 表示一个范围，a -> b 表示另一个范围。

重叠情况 1

A           B
*----------*
     a           b
     *----------*

重叠情况 2

     A           B
     *----------*
a           b
*----------*

重叠情况 3

     A           B
     *----------*
a                        b
*---------------------*

重叠情况 4 - 当 a 或 b 与 A 或 B 相同时

a == A || a == B || b == A || b == B  

通过检查这四种情况，不同的场景将得到实现。

我们的逻辑应该检查所有场景。

(scenario 1 (+4) || scenario 2 (+4)  || scenario 3 )

(a >= A && a <= B) || (b >= A && b <= B) || (a < A && b > B)

- DevWL

Answer 4

我认为你有一个多余的测试。前两个语句将覆盖两个范围在一端重叠或第一个范围完全包含在第二个范围内的情况。

然后，您需要测试第二个范围是否完全包含在第一个范围内，其他任何情况都将在前两个测试中涵盖。为此，您只需要测试开始或结束。

因此，我认为这将起作用。

 $query = "SELECT u.first_name, u.last_name, u.avatar_filename, p.id, p.user_id, p.address, p.topic, p.latitude, p.longitude, d.name AS department_name
              FROM user u
              JOIN placement p ON p.user_id=u.id
              JOIN department d ON d.id = u.department_id
              WHERE p.active=1 AND (('{$dateA}' BETWEEN p.date_started AND p.date_finished) OR 
                                    ('{$dateB}' BETWEEN p.date_started AND p.date_finished) OR
                                    (p.date_started BETWEEN '{$dateA}' AND '{$dateB}'))";

- Born2Code