选择开始/结束日期之间的事件的MySQL查询

Question

我有一个名为“events”的MySQL表，其中包含活动数据。重要的列是“start”和“end”，它们包含字符串（YYYY-MM-DD），用于表示活动开始和结束的时间。

我想要获取在某一时段内所有活动的记录。

活动：

------------------------------
ID | START      | END        |
------------------------------
1  | 2013-06-14 | 2013-06-14 |
2  | 2013-06-15 | 2013-08-21 |
3  | 2013-06-22 | 2013-06-25 |
4  | 2013-07-01 | 2013-07-10 |
5  | 2013-07-30 | 2013-07-31 |
------------------------------

请求/搜索：

示例：在2013-06-13和2013-07-22之间的所有事件：#1、#3、#4
SELECT id FROM events WHERE start BETWEEN '2013-06-13' AND '2013-07-22' : #1, #2, #3, #4
SELECT id FROM events WHERE end   BETWEEN '2013-06-13' AND '2013-07-22' : #1, #3, #4
====> 相交：#1、#3、#4

示例：在2013-06-14和2013-06-14之间的所有事件：
SELECT id FROM events WHERE start BETWEEN '2013-06-14' AND '2013-06-14' : #1
SELECT id FROM events WHERE end   BETWEEN '2013-06-14' AND '2013-06-14' : #1
====> 相交：#1

我尝试了很多查询，但仍然无法得到准确的SQL查询。

你不知道如何做吗？有什么建议吗？

谢谢！

- Guicara

---

你想要一个单一的查询吗？ - Fabio

我正在寻找一种更优雅的查询方式。我相当确定这个查询语句不是很好。我已经尝试过使用“BETWEEN start AND end”，但没有成功。问题出在当开始日期等于结束日期时。 - Guicara

@Guicara 在我的答案中添加了起始和结束条件。 - echo_Me

你的回答帮了我很多，谢谢。创建这个主题时，我认为我的问题或者疑问在某些情况下是不好的，但实际上并非如此！当我们卡在一个问题上很长时间时，答案可能会突然出现在你眼前，而你却没有看到它。 - Guicara

在你的第一个例子中，＃2是你的范围内正在进行的事件。持续发生的事件不是与活动事件相同的事情吗？如果举个例子：2013-06-22和2013-06-23之间的所有事件[活动]，那么＃2和＃3都将是活动事件吗？ - amaster

Answer 1

如果我理解正确，您正在尝试使用单个查询，我认为您可以将日期搜索合并到 WHERE 子句中。

SELECT id 
FROM events 
WHERE start BETWEEN '2013-06-13' AND '2013-07-22' 
AND end BETWEEN '2013-06-13' AND '2013-07-22'

甚至更简单的方法是只使用两栏来设置搜索时间过滤器。

SELECT id 
FROM events 
WHERE start >= '2013-07-22' AND end <= '2013-06-13'

- Fabio

---

你们两个同时回答了 :) 你们的回答对我很有帮助，谢谢。创建这个主题时，我认为我的答案（我的问题）在某些情况下很糟糕，但实际上并不是！当我们卡在一个问题上很长时间时，这就是问题所在：答案可能会在你看不到它的情况下跳出来。 - Guicara

@Guicara，很高兴我能帮到你，请考虑接受一个答案，选择最好的一个来关闭你的问题。 - Fabio

@echo_Samir，我给你一个“+1”，并选择了Fabio的答案。我本来想验证你的两个答案，但Stackoverflow不允许这样做。 - Guicara

@echo_Samir 我认为OP可以自行决定哪个答案对他来说更好，他不应该只接受第一个答案，而是选择他认为最好的答案。 - Fabio

此答案不包括正在发生的事件。依我之见，那些可能在时间范围之前或之后开始或结束的正在进行事件应该是“某段时间内所有活动事件”的一部分。 - amaster

Answer 2

您需要在范围内开始和结束的事件。但这还不够：您还想要在范围内开始的事件和在范围内结束的事件。但是，您仍然不满足，因为您还想要在范围之前开始并在范围之后结束的事件。

简化：

1. 在范围内有开始日期的事件
2. 在范围内有结束日期的事件
3. 在开始日期和结束日期之间有范围开始日期的事件

因为第2点的结果也符合第3点的查询，所以我们只需要第1点和第3点。

因此SQL语句如下：

SELECT * FROM events 
WHERE start BETWEEN '2014-09-01' AND '2014-10-13' 
OR '2014-09-01' BETWEEN start AND end

- Peter de Groot

---

即使这是一个从MySQL移植到PostgreSQL并经过一些微调后运行良好的查询，我还是欠你一杯啤酒！ - Enrique Benitez

这个问题可能可以用Olivier的回答来简化。至少这个答案包括了跨范围的事件。 - amaster

这是唯一正确的答案，不是吗？ - zywx

这是不正确的，有四种情况：（3）范围完全包含事件（4）事件完全包含范围。 - Salman A

Answer 3

这里有很多好的答案，但我认为这个会帮助某些人

select id  from campaign where ( NOW() BETWEEN start_date AND end_date) 

- Saurabh Chandra Patel

---

有所帮助，但并不是问题的答案。虽然这比一些没有包括正在进行事件的答案要好。 - amaster

完美的解决方案！ - Marko Šutija

那是不正确的。现在不是一个时间段，正如问题所述。现在只是一个时间点。 - Salman A

Answer 4

SELECT id
FROM events
WHERE start <= '2013-07-22'
AND end >= '2013-06-13';

如果您不知道优先级，请使用MIN()和MAX()。

- Olivier Coilland

---

谢谢你的回答！它有效了，但我认为你颠倒了开始/结束日期。正确的查询应该是：SELECT id FROM events WHERE start <= '2013-06-13' AND end >= '2013-07-22'。 - Guicara

@Guicara，这实际上是这里列出的唯一正确答案之一。让我解释一下。假设一个事件从2013年1月1日开始，到2013年12月31日结束。这个事件应该包含在查询中吗？我认为应该，因为它是在您选择的时间范围内持续进行的活动事件。除非您的意思是“在时间段内所有活动事件”仅指在日期范围内开始和结束的事件。最近我在我的脚本中遇到了这个“错误”，因为我使用了其他答案之一。我认为您想要的是在结束日期之前开始并在开始日期之后结束的事件。 - amaster

@amaster 没错...你说得对。感谢您分享关于这个问题非常深思熟虑的见解。我真的很感激您提供有用的评论所付出的努力。 - Sandeep Garg

Answer 5

SELECT *
FROM events 
WHERE endDate >= @startDate AND startDate <= @endDate

请参考以下图示进行说明：

• 假设样本数据为[startDate: 2020-10-01, endDate: 2020-20-01]
• 用户提供@startDate和@endDate来查询
• 对于重叠情况，有4种场景和1种变化（红色/褐色线条）
• 对于没有重叠的情况，只有2种情形（绿色线条）

因此，要通过提供起始日期和结束日期来获取重叠日期，必须使endDate大于@startDate并且startDate小于@endDate。

- Mohammad Sarfaraz

---

能否解释一下？另外，你为什么使用了 nolock，它与问题有什么关系？ - endo64

@endo64 已添加解释。 - Mohammad Sarfaraz

Answer 6

SELECT * 
FROM events 
WHERE start <= '2013-07-22' OR end >= '2013-06-13'

- Philip Sheard

---

我认为这里有一个打字错误或者是错误的答案。应该是 WHERE start <= ToDate OR end >= FromDate。 - amaster

由于 StackOverflow 标记了多个用户，因此我已删除了评论中的 @ 符号。 - amaster

谢谢。我已经恢复了我的原始SQL。有人无缘无故地编辑了它，破坏了它的简洁性。我相信我的解决方案是最简单的。 - Philip Sheard

Answer 7

编辑：我已经压缩了过滤器。之前我无法理解如何确保某些内容真正适合于时间段内。它是这样的：开始日期在时间段结束之前，结束日期在时间段开始之后。

在我的办公室里有人帮助我，我想我们已经找出了如何将所有人包含在过滤器中。有5种情况下，学生在所涉及的时间段内被视为活跃：

1）学生在此期间开始并结束。

2）学生在此期间之前开始并在此期间结束。

3）学生在此期间之前开始并在此期间之后结束。

4）学生在此期间开始并在此期间之后结束。

5）学生在此期间开始并仍处于活动状态（尚未有结束日期）。

根据这些标准，我们实际上可以将语句压缩成几个组，因为学生只能在期间日期之间结束，在期间日期之后结束，或者他们没有结束日期：

1）学生在此期间结束，并且[学生在此期间之前或之中开始]

2）学生在此期间之后结束，并且[学生在此期间之前或之中开始]

3）学生尚未完成且[学生开始之前或期间]

   (
        (
         student_programs.END_DATE  >=  '07/01/2017 00:0:0'
         OR
         student_programs.END_DATE  Is Null  
        )
        AND
        student_programs.START_DATE  <=  '06/30/2018 23:59:59'
   )

我认为这最终涵盖了所有情况，包括在仅有开始日期和结束日期的时间段内，学生、事件或任何事物处于活动状态的所有场景。请不要犹豫告诉我是否遗漏了什么。我希望这能够完美无缺，以便其他人可以使用，因为我认为其他答案还没有完全正确。

- Lord Bobbymort

Answer 8

如果您想使用INTERSECT选项，SQL如下所示。

(SELECT id FROM events WHERE start BETWEEN '2013-06-13' AND '2013-07-22') 
INTERSECT
(SELECT id FROM events WHERE end BETWEEN '2013-06-13' AND '2013-07-22') 

- bkm

---

那么在作用域之前开始并在作用域之后结束的事件呢？它们不也应该被视为活动事件吗？持续中=活动中？你的回答只检索在作用域内开始和结束的事件。 - amaster

如何选择所有事件日期时间在1小时之前的记录，例如mysql选择所有时间差小于datetime的1小时？这是为了一个定期任务上的提醒电子邮件，你能帮忙编写这个SQL查询吗？ - Jay Mee

Answer 9

如果有人在Microsoft SQL中搜索当前日期位于两个时间段（开始/结束日期）之间的情况，请查看以下内容

select id from campaign where (getdate() BETWEEN start_date AND end_date) 

- Vinu V

---

关于一个事件开始时间前1小时的提醒，我该如何实现？（我无法发起问题，它不允许我这样做）？ - Jay Mee

Answer 10

试一下这个

    SELECT id FROM events WHERE start BETWEEN '2013-06-13' AND '2013-07-22' 
                          AND   end   BETWEEN '2013-06-13' AND '2013-07-22'

这里演示

输出：

 ID
 1
 3
 4

- echo_Me

---

谢谢，但是“并且开始时间不等于结束时间”是不必要/错误的（因为我可能有一个在同一天开始和结束的事件）。 - Guicara

好的，这只是因为你在问题中提到了开始和结束日期相同的问题。无论如何，我已经编辑了我的答案。 - echo_Me

这不会检索开始时间早于并且结束时间晚于的事件；开始时间早于并且结束时间在期间内的事件；或者开始时间在期间内并且结束时间晚于的事件。在我看来，正在进行的事件是“时间段内活动的事件”。 - amaster