pg.ddx.io pgsql-bugs@postgresql.org mailing list archivehelp / color / mirror / Atom feed
BUG #16747: Unexpected behaviour of the overlaps function 4+ messages / 3 participants [nested] [flat]
* BUG #16747: Unexpected behaviour of the overlaps function @ 2020-11-26 15:24 PG Bug reporting form <noreply@postgresql.org> 2020-11-26 16:04 ` Re: BUG #16747: Unexpected behaviour of the overlaps function David G. Johnston <david.g.johnston@gmail.com> 2020-11-26 16:21 ` Re: BUG #16747: Unexpected behaviour of the overlaps function Павел Лучин <ddiamondbbackk@gmail.com> 2020-11-26 16:26 ` Re: BUG #16747: Unexpected behaviour of the overlaps function Павел Лучин <ddiamondbbackk@gmail.com> 0 siblings, 3 replies; 4+ messages in thread From: PG Bug reporting form @ 2020-11-26 15:24 UTC (permalink / raw) To: pgsql-bugs@lists.postgresql.org; +Cc: ddiamondbbackk@gmail.com 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? ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #16747: Unexpected behaviour of the overlaps function 2020-11-26 15:24 BUG #16747: Unexpected behaviour of the overlaps function PG Bug reporting form <noreply@postgresql.org> @ 2020-11-26 16:04 ` David G. Johnston <david.g.johnston@gmail.com> 2 siblings, 0 replies; 4+ messages in thread From: David G. Johnston @ 2020-11-26 16:04 UTC (permalink / raw) To: ddiamondbbackk@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org> 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. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #16747: Unexpected behaviour of the overlaps function 2020-11-26 15:24 BUG #16747: Unexpected behaviour of the overlaps function PG Bug reporting form <noreply@postgresql.org> @ 2020-11-26 16:21 ` Павел Лучин <ddiamondbbackk@gmail.com> 2 siblings, 0 replies; 4+ messages in thread From: Павел Лучин @ 2020-11-26 16:21 UTC (permalink / raw) To: Павел Лучин <ddiamondbbackk@gmail.com>; pgsql-bugs@lists.postgresql.org 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 >: > 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? > > ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #16747: Unexpected behaviour of the overlaps function 2020-11-26 15:24 BUG #16747: Unexpected behaviour of the overlaps function PG Bug reporting form <noreply@postgresql.org> @ 2020-11-26 16:26 ` Павел Лучин <ddiamondbbackk@gmail.com> 2 siblings, 0 replies; 4+ messages in thread From: Павел Лучин @ 2020-11-26 16:26 UTC (permalink / raw) To: Павел Лучин <ddiamondbbackk@gmail.com>; pgsql-bugs@lists.postgresql.org 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 >: > 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? > > ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2020-11-26 16:26 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-11-26 15:24 BUG #16747: Unexpected behaviour of the overlaps function PG Bug reporting form <noreply@postgresql.org> 2020-11-26 16:04 ` David G. Johnston <david.g.johnston@gmail.com> 2020-11-26 16:21 ` Павел Лучин <ddiamondbbackk@gmail.com> 2020-11-26 16:26 ` Павел Лучин <ddiamondbbackk@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox