BUG #16747: Unexpected behaviour of the overlaps function
The following bug has been logged on the website:
Bug reference: 16747
Logged by: Paul Luchyn
Email address: ddiamondbbackk@gmail.com
PostgreSQL version: 11.8
Operating system: Windows
Description:
Hello!
I'm working with the "overlaps" function.
In my humble opinion it works incorrectly in one case.
I have prepared some examples.
1) Two intervals with zero duration: SELECT (TIMESTAMP '2020-11-29
12:00:00.000', TIMESTAMP '2020-11-29 12:00:00.000') OVERLAPS (TIMESTAMP
'2020-11-29 12:00:00.000', TIMESTAMP '2020-11-29 12:00:00.000'); ,the result
is "true", looks fine.
2) Interval with zero duration, and non-zero interval: SELECT (TIMESTAMP
'2020-11-29 12:00:00.000', TIMESTAMP '2020-11-29 12:00:00.000') OVERLAPS
(TIMESTAMP '2020-11-29 12:00:00.000', TIMESTAMP '2020-11-29 12:02:00.000');
,the result is "true", looks fine.
3) Two intervals with non-zero duration: SELECT (TIMESTAMP '2020-11-29
12:00:00.000', TIMESTAMP '2020-11-29 12:01:00.000') OVERLAPS (TIMESTAMP
'2020-11-29 12:01:00.000', TIMESTAMP '2020-11-29 12:02:00.000'); , the
result is false. The last case looks suspicious: logically these two
intervals don't overlap, but they have common moment of time (2020-11-29
12:01:00.000).
And one more question: is there any other function which will do the trick
in the situation described in the last example?
On Thu, Nov 26, 2020 at 8:56 AM PG Bug reporting form <
noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 16747
Logged by: Paul Luchyn
Email address: ddiamondbbackk@gmail.com
PostgreSQL version: 11.8
Operating system: Windows
Description:Hello!
I'm working with the "overlaps" function.
In my humble opinion it works incorrectly in one case.
Your examples perfectly match the documentation:
"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."
https://www.postgresql.org/docs/13/functions-datetime.html
And one more question: is there any other function which will do the trick
in the situation described in the last example?
Create an explicit range out of the timestamps and use one of the range
operators/functions?
https://www.postgresql.org/docs/current/functions-range.html
David J.
How can I avoid half-open interval behavior?
I can not find any examples in the documentation...
чт, 26 нояб. 2020 г. в 17:25, PG Bug reporting form <noreply@postgresql.org
Show quoted text
:
The following bug has been logged on the website:
Bug reference: 16747
Logged by: Paul Luchyn
Email address: ddiamondbbackk@gmail.com
PostgreSQL version: 11.8
Operating system: Windows
Description:Hello!
I'm working with the "overlaps" function.
In my humble opinion it works incorrectly in one case.
I have prepared some examples.1) Two intervals with zero duration: SELECT (TIMESTAMP '2020-11-29
12:00:00.000', TIMESTAMP '2020-11-29 12:00:00.000') OVERLAPS (TIMESTAMP
'2020-11-29 12:00:00.000', TIMESTAMP '2020-11-29 12:00:00.000'); ,the
result
is "true", looks fine.
2) Interval with zero duration, and non-zero interval: SELECT (TIMESTAMP
'2020-11-29 12:00:00.000', TIMESTAMP '2020-11-29 12:00:00.000') OVERLAPS
(TIMESTAMP '2020-11-29 12:00:00.000', TIMESTAMP '2020-11-29 12:02:00.000');
,the result is "true", looks fine.
3) Two intervals with non-zero duration: SELECT (TIMESTAMP '2020-11-29
12:00:00.000', TIMESTAMP '2020-11-29 12:01:00.000') OVERLAPS (TIMESTAMP
'2020-11-29 12:01:00.000', TIMESTAMP '2020-11-29 12:02:00.000'); , the
result is false. The last case looks suspicious: logically these two
intervals don't overlap, but they have common moment of time (2020-11-29
12:01:00.000).And one more question: is there any other function which will do the trick
in the situation described in the last example?
Looks like I've found the problem.
I should pass the range type.
The following piece of code looks like working:
SELECT tsrange(TIMESTAMP '2020-11-29 12:00:00.000', TIMESTAMP '2020-11-29
12:01:00.000', '[]') && tsrange(TIMESTAMP '2020-11-29 12:01:00.000',
TIMESTAMP '2020-11-29 12:02:00.000', '[]');
чт, 26 нояб. 2020 г. в 17:25, PG Bug reporting form <noreply@postgresql.org
Show quoted text
:
The following bug has been logged on the website:
Bug reference: 16747
Logged by: Paul Luchyn
Email address: ddiamondbbackk@gmail.com
PostgreSQL version: 11.8
Operating system: Windows
Description:Hello!
I'm working with the "overlaps" function.
In my humble opinion it works incorrectly in one case.
I have prepared some examples.1) Two intervals with zero duration: SELECT (TIMESTAMP '2020-11-29
12:00:00.000', TIMESTAMP '2020-11-29 12:00:00.000') OVERLAPS (TIMESTAMP
'2020-11-29 12:00:00.000', TIMESTAMP '2020-11-29 12:00:00.000'); ,the
result
is "true", looks fine.
2) Interval with zero duration, and non-zero interval: SELECT (TIMESTAMP
'2020-11-29 12:00:00.000', TIMESTAMP '2020-11-29 12:00:00.000') OVERLAPS
(TIMESTAMP '2020-11-29 12:00:00.000', TIMESTAMP '2020-11-29 12:02:00.000');
,the result is "true", looks fine.
3) Two intervals with non-zero duration: SELECT (TIMESTAMP '2020-11-29
12:00:00.000', TIMESTAMP '2020-11-29 12:01:00.000') OVERLAPS (TIMESTAMP
'2020-11-29 12:01:00.000', TIMESTAMP '2020-11-29 12:02:00.000'); , the
result is false. The last case looks suspicious: logically these two
intervals don't overlap, but they have common moment of time (2020-11-29
12:01:00.000).And one more question: is there any other function which will do the trick
in the situation described in the last example?