SQL OVERLAPS 运算符问题，如何摆脱它

Question

我预计从“2011-01-28”到“2011-02-01”的日期段与从“2011-02-01”到“2011-02-01”的日期段重叠(这里是同一天)，但事实并非如此!

PostgreSQL 期望精确结束点的匹配不是匹配...如何摆脱这个？我想让它把上述情况视为重叠。

SELECT (DATE '2011-01-28', DATE '2011-02-01') OVERLAPS
       (DATE '2011-02-01', DATE '2011-02-01');

返回 false，而我希望它返回 true。

Answer 1

你的期望错了。来自fine manual :

Each time period is considered to represent the half-open interval start <= time < end, unless start and end are 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，那么您可以按照 Catcall 的建议明确地进行终点检查，或者您可以将一天添加到上限绑定(bind):

SELECT (DATE '2011-01-28', DATE '2011-02-01' + 1) OVERLAPS
       (DATE '2011-02-01', DATE '2011-02-01'    )

但要注意将端点按正确的顺序排列为:

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.