SQL查找跨越午夜（跨越两天）的时间重叠。

Question

我知道有很多类似的问题，但是我没有看到一个足够符合我的条件。所以我想请你帮忙。我拥有的字段只是开始时间和结束时间，它们都是时间类型。我不能涉及任何具体日期。如果时间范围不跨越午夜，我会像这样比较两个元组：

end1 > start2 AND start1 < end2

（这里不考虑端点相接的情况）

但是当我涉及跨越午夜的时间范围时，这显然行不通。例如，给定：

 start  |   end
--------+--------
06:00PM | 01:00AM
03:00PM | 09:00PM

请不要涉及日期，我该如何实现这一点。我的假设是，如果结束时间小于开始时间，则涉及2天。

我正在尝试在纯标准 SQL 中完成此操作，因此只需在 WHERE 子句中使用简单而简洁的逻辑即可。

谢谢大家！

---

补充：

另外，我该如何测试一个时间范围是否完全包含另一个时间范围？再次感谢！

- janechii

---

@janechii：欢迎来到StackOverflow！您可以通过编辑标签以反映您正在使用的数据库（oracle，mysql，sql-server，...）来改进您的问题。 - Peter Lang

@janechii - 你能否也展示一下表格模式？ - Thomas

@Peter - 谢谢。我特别使用postgresql，但我也想让它在其他数据库上运行，请帮忙。 - janechii

Answer 1

如果你的SQL支持时间差：

(end1 - start1) > (start2 - start1) AND (end2 - start2) > (start1 - start2)

- Doug Currie

---

哦，这真是好东西，谢谢！所以，逻辑是开始时间之间的差应该大于持续时间吗？ - janechii

持续时间应该始终为正数，我认为。因此：abs(end1 - start1) > (start2 - start1) AND abs(end2 - start2) > (start1 - start2) - janechii

这有点巧妙！我的推导是通过代数而非直觉得出的：取你的表达式，从左侧比较中两边减去start1，在右侧比较中两边减去start2。这意味着：event1的持续时间必须超过event1开始和event2开始之间的时间，而event2的持续时间必须超过event2开始和event1开始之间的时间。这种对称性很令人愉悦。 - Doug Currie

@janechii，关于abs函数，我假设时间差是“准确的”，例如使用模运算来计算起始和结束时间。 - Doug Currie

Answer 2

不幸的是，“普通”的SQL语句在实际数据库中使用起来太过笼统。原因是各种数据库产品对于计算两个时间之间持续时间的支持程度不同。例如，在SQL Server 2008中，将时间值转换为DateTime然后进行比较会更简单，因为许多比较运算符不支持Time数据类型。

Select ...
From    (
        Select Cast(T.Start1 As DateTime) As Start1
            , Case
                When Cast(T.Start1 As DateTime) > Cast(T.End1 As DateTime) Then DateAdd(d,1,Cast(T.End1 As DateTime))
                Else Cast(T.End1 As DateTime)
                End As End1
        From ...
        ) As T
Where T.End1 > T2.Start2 And T1.Start2 < T2.End2            

- Thomas

---

是的，你说得对，日期和时间处理在不同的数据库之间似乎有很大的差异。我特别使用的是PostgreSQL，但这个解决方案是一个聪明的方法，也应该适用于PG。谢谢！ - janechii

Answer 3

使用开始时间和持续时间（以分钟或适当的单位）

- Steven A. Lowe

Answer 4

该程序Transtar存在问题。时间数据既没有与任何日期相关联，也不在日期时间字段中。该程序最初设计用于从凌晨4点到午夜发出过境行程单，只要过境不是全天候的就可以正常工作。我编写了一个函数，对时间进行滑动测试，因此如果您要求5点钟，它将查看从1点到12:59 AM的时间。我用FORTRAN编写了它，但无论使用哪种语言，算法都是相同的。

- Dave

---

谢谢你的建议，Dave！我曾经尝试过这个想法，但对其他维护者来说似乎有些困惑。不过对于现有系统来说，这是一个不错的解决方案。 - janechii

同意 - 非常令人困惑 - 必须认识到的主要事情是时间数据是模块化的，而真实时间则不是。 - Dave