postgresql SQL OVERLAPS 运算符问题，如何解决
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5218370/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL OVERLAPS operator problem, how to get rid of it
提问by Mellon
I expect that date period from '2011-01-28' to '2011-02-01' OVERLAPS period from '2011-02-01' to '2011-02-01' (that's the same day here), but it does not!
我希望从 '2011-01-28' 到 '2011-02-01' OVERLAPS 期间从 '2011-02-01' 到 '2011-02-01'（这是同一天），但它确实不是！
PostgreSQL expecting the match of exact ending point is NOT a match...how to get rid of this? I would like to have it treat the above scenario as overlap.
PostgreSQL 期望精确结束点的匹配不是匹配......如何摆脱这个？我希望将上述情况视为重叠。
SELECT (DATE '2011-01-28', DATE '2011-02-01') OVERLAPS
(DATE '2011-02-01', DATE '2011-02-01');
returns false, while I expect it to return true.
返回 false，而我希望它返回 true。
采纳答案by Milen A. Radev
A kind of a workaround, i.e. it may or may not make sense for your case - convert the dates to timestamps:
一种解决方法，即它可能对您的情况有意义也可能没有意义 - 将日期转换为时间戳：
SELECT (TIMESTAMP '2011-01-28 00:00:00', TIMESTAMP '2011-02-01 23:59:59') OVERLAPS (TIMESTAMP '2011-02-01 00:00:00', TIMESTAMP '2011-02-01 23:59:59');
Technically it's sufficient to convert only the endpoint of the first period, at least for the example you've given.
从技术上讲，仅转换第一个时期的端点就足够了，至少对于您给出的示例而言。
回答by mu is too short
You expect wrong. From the fine manual:
你期望错了。从精美的手册：
Each time period is considered to represent the half-open interval start <= time < end, unless startand endare equal in which case it represents that single time instant. This means for instance that two time periods with only an endpoint in common do not overlap.
每个时间段都被认为代表半开区间start <= time < end，除非start和end相等，在这种情况下它代表单个时间瞬间。这意味着例如只有一个共同端点的两个时间段不重叠。
So, if you want the closed interval, start <= time <= end, then you can either do end-point checks explicitly as Catcall suggests or you can add a single day to the upper bound:
因此，如果您想要闭合间隔，start <= time <= end，那么您可以按照 Catcall 的建议明确进行终点检查，也可以将一天添加到上限：
SELECT (DATE '2011-01-28', DATE '2011-02-01' + 1) OVERLAPS
(DATE '2011-02-01', DATE '2011-02-01' )
But be careful to put the end-points in the correct order as:
但请注意将端点按正确的顺序排列为：
When a pair of values is provided, either the start or the end can be written first; OVERLAPS automatically takes the earlier value of the pair as the start.
当提供一对值时，可以先写开始或结束；OVERLAPS 自动以该对的较早值作为开始。
回答by Mike Sherrill 'Cat Recall'
I think the simplest way is to use the overlaps operator as is, and add to the WHERE clause to treat adjoining dates specially.
我认为最简单的方法是按原样使用重叠运算符，并添加到 WHERE 子句以特殊处理相邻日期。
Assuming
假设
- (S1, E1) are the start and end dates of the first range,
- (S2, E2) are the start and end dates of the second range,
- none of S1, E1, S2, or E2 are NULL,
- (S1, E1) 是第一个范围的开始和结束日期，
- (S2, E2) 是第二个范围的开始和结束日期，
- S1、E1、S2 或 E2 都不是 NULL，
then this should return a row when the ranges either overlap or adjoin each other.
那么当范围重叠或相邻时，这应该返回一行。
select S1, E1, S2, E2
from [some table name]
where (S1, E1) overlaps (S2, E2)
or (E1 = S2) -- first range ends on the start date of the second
or (E2 = S1) -- second range ends on the start date of the first
回答by Má?a - Stitod.cz
Why not use classic query:
为什么不使用经典查询：
select (date '2013-01-01' between date '2012-12-01' and date '2013-01-01') or
(date '2013-03-01' between date '2012-12-01' and date '2013-01-01');
In pseudocode:
在伪代码中：
select (date 'START1' between date 'START2' and date 'END2') or
(date 'END1' between date 'START2' and date 'END2');
Result will be same though order will be changed:
结果将相同，但顺序将更改：
select (date 'START2' between date 'START1' and date 'END1') or
(date 'END2' between date 'START1' and date 'END1');
