在MySQL中检查日期范围的重叠

Question

这个表用于存储会话（事件）：

CREATE TABLE session (
  id int(11) NOT NULL AUTO_INCREMENT
, start_date date
, end_date date
);

INSERT INTO session
  (start_date, end_date)
VALUES
  ("2010-01-01", "2010-01-10")
, ("2010-01-20", "2010-01-30")
, ("2010-02-01", "2010-02-15")
;

我们不希望范围之间存在冲突。
假设我们需要在2010-01-05到2010-01-25之间插入一个新的会话。
我们想知道是否存在冲突的会话。

以下是我的查询：

SELECT *
FROM session
WHERE "2010-01-05" BETWEEN start_date AND end_date
   OR "2010-01-25" BETWEEN start_date AND end_date
   OR "2010-01-05" >= start_date AND "2010-01-25" <= end_date
;

以下是结果：

+----+------------+------------+
| id | start_date | end_date   |
+----+------------+------------+
|  1 | 2010-01-01 | 2010-01-10 |
|  2 | 2010-01-20 | 2010-01-30 |
+----+------------+------------+

有更好的方法可以得到那个吗？

---

fiddle

- Pierre de LESPINAY

---

你的第三个条件是错误的。应该是 "2010-01-05" <= start_date AND "2010-01-25" >= end_date。请参考 https://dev59.com/cnbZa4cB1Zd3GeqPF3AE#28802972 进行可视化。你当前的第三个条件永远不会被评估，因为第一（和第二）个条件已经涵盖了它。 - Pacerier

Answer 1

我曾经在一个日历应用程序中遇到这样的问题。我认为我使用了类似于以下内容的代码：

... WHERE new_start < existing_end
      AND new_end   > existing_start;

更新 这肯定会起作用((ns, ne, es, ee) = (new_start, new_end, existing_start, existing_end))：

1. ns - ne - es - ee：不重叠并且不匹配(因为ne < es)
2. ns - es - ne - ee：重叠并且匹配
3. es - ns - ee - ne：重叠并且匹配
4. es - ee - ns - ne：不重叠并且不匹配(因为ns > ee)
5. es - ns - ne - ee：重叠并且匹配
6. ns - es - ee - ne：重叠并且匹配

---

这里是一个 fiddle

- soulmerge

---

非常好用！但我认为@Pierre de LESPINAY在他的查询中正在寻找包括范围：WHERE new_start <= existing_end AND new_end >= existing_start; - Osvaldo Mercado

@OsvaldoM. 如果他真的是那样，他早就会在两年前抱怨了... - soulmerge

今天我的一个事件结束了，另一个事件也在今天开始了，它们会重叠吗？在我看来，它们会重叠。但是 SQL 查询不会表明这一点：http://sqlfiddle.com/#!2/0a6fd/1/0 - A.L

@soulmerge，实际上，他只需将 = 添加到他的实际代码中，而不必费心抱怨它。 - Pacerier

Answer 2

SELECT * FROM tbl WHERE
existing_start BETWEEN $newStart AND $newEnd OR 
existing_end BETWEEN $newStart AND $newEnd OR
$newStart BETWEEN existing_start AND existing_end

if (!empty($result))
throw new Exception('We have overlapping')

这3行SQL语句涵盖了4种重叠情况的要求。

- Yasen

---

即使原帖作者似乎并不寻求这种重叠的定义，但这个答案是描述问题的最佳解决方案。我正在寻找这个重叠部分，这才是真正的重叠部分。 - Cec

我不确定你所说的“真正的重叠区域”是什么意思，但@soulmerge提供的最佳答案与Lamy的答案等效。我使用Z3定理求解器证明了它们的等效性：http://www.grantjenks.com/projects/equivalent-inequalities/ - GrantJ

Answer 3

Lamy的回答很好，但您可以进一步优化它。

SELECT * FROM tbl WHERE
existing_start BETWEEN $newSTart AND $newEnd OR
$newStart BETWEEN existing_start AND existing_end

这将捕获所有四种范围重叠的情况并排除两种不重叠的情况。

- LordJavac

---

除了上述两种解决方案之外，还有其他的解决方案吗？ - Pacerier

Answer 4

我曾经遇到过类似的问题。我的问题是如何在一段被禁止预定的日期范围内停止预定。例如，某个物业在5月2日至5月7日之间无法预订。我需要找到任何重叠的日期来检测和停止预订。我的解决方案与LordJavac类似。

SELECT * FROM ib_master_blocked_dates WHERE venue_id=$venue_id AND 
(
    (mbd_from_date BETWEEN '$from_date' AND '$to_date') 
    OR
    (mbd_to_date BETWEEN  '$from_date' AND '$to_date')
    OR
    ('$from_date' BETWEEN mbd_from_date AND mbd_to_date)
    OR      
    ('$to_date' BETWEEN mbd_from_date AND mbd_to_date)      
)
*mbd=master_blocked_dates

如果不能正常工作，请告诉我。

- Niraj Kumar

Answer 5

给定两个区间 (s1, e1) 和 (s2, e2)，其中 s1<e1 且 s2<e2。您可以按以下方式计算重叠部分：

SELECT 
     s1, e1, s2, e2,
     ABS(e1-s1) as len1,
     ABS(e2-s2) as len2,
     GREATEST(LEAST(e1, e2) - GREATEST(s1, s2), 0)>0 as overlaps,
     GREATEST(LEAST(e1, e2) - GREATEST(s1, s2), 0) as overlap_length
FROM test_intervals 

如果一个时间段包含另一个时间段，也同样适用。

- Mackraken

Answer 6

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

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

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

- Hussein Akar

Answer 7

最近我也遇到了同样的问题，最终通过一个简单的步骤解决了它（这可能不是一个好的方法或会占用内存）-

SELECT * FROM duty_register WHERE employee = '2' AND (
(
duty_start_date BETWEEN {$start_date} AND {$end_date}
OR
duty_end_date BETWEEN {$start_date} AND {$end_date}
)
OR
(
{$start_date} BETWEEN duty_start_date AND duty_end_date
OR
{$end_date} BETWEEN duty_start_date AND duty_end_date)
);

这帮助我找到了具有重叠日期范围的条目。

希望这能帮助到某些人。

- Vishal Kumar Sahu

Answer 8

Mackraken在上面的回答从性能角度来看更好，因为它不需要多个OR来评估两个日期是否重叠。很好的解决方案！

然而，我发现在MySQL中，你需要使用DATEDIFF而不是减号运算符 -。

SELECT o.orderStart, o.orderEnd, s.startDate, s.endDate
, GREATEST(LEAST(orderEnd, endDate) - GREATEST(orderStart, startDate), 0)>0 as overlaps
, DATEDIFF(LEAST(orderEnd, endDate), GREATEST(orderStart, startDate)) as overlap_length
FROM orders o
JOIN dates s USING (customerId)
WHERE 1
AND DATEDIFF(LEAST(orderEnd, endDate),GREATEST(orderStart, startDate)) > 0;

- yg-dba