Re: Finding overlapping records

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Some additional clarification from a email directly to me...
- --

Jon T Erdman (aka StuckMojo)
PostgreSQL Zealot

- -------- Original Message --------
Subject: Re: in response to finding overlapping records
Date: Wed, 30 Dec 2009 16:46:42 -0500 (EST)
From: Michael Alaimo <malaimo(at)sesda2(dot)com>
To: Jon Erdman <jon(at)progressivepractice(dot)com>
References: <48435(dot)128(dot)183(dot)191(dot)6(dot)1262201660(dot)squirrel(at)mail(dot)sesda2(dot)com>
<4B3BC174(dot)3040206(at)progressivepractice(dot)com>

> Michael Alaimo wrote:
>> Hello Jon,
>>
>> I was reading the mail archives located here:
>> http://archives.postgresql.org/austinpug/2009-12/msg00016.php.
>>
>> I cannot seem to get the same results with your query as with overlaps.
>> Are you sure thats what was in the book SQL for smarties?
>>
>> Just checking. I am having a really difficult time with overlaps and no
>> index support..... My queries are dragging.
>
> Ummm...I may have done it slightly different than OVERLAPS as far as
> inclusive vs exclusive (i.e. >= vs >)...let me check...actually it looks
> like I did it right. Could you send me your table definitions and the
> two versions of the queries you're using? As well as an example of the
> differing results?
>
> Hmm, upon further inspection, he does mention that: "please remember
> that the BETWEEN predicate will include the end point of an interval,
> and the OVERLAPS predicate will not."
>
> He also says: The result of the <overlaps predicate> is formally defined
> as the result of the following expression:
>
> (s1 > s2 AND NOT (s1 >= t2 AND t1 >= t2))
> OR (s2 > s1 AND NOT (s2 >= t1 AND t2 >= t1))
> OR (s1 = s2 AND (t1 <> t2 OR t1 = t2))
>
> where s1 and s2 are the starting times of the two time periods, and t1
> and t2 are their termination times.
> --
>
> Jon T Erdman
>
> Chief Information Officer voice: (210) 400-5717
> Progressive Practice, Inc. jon(at)progressivepractice(dot)com
> P.O. Box 17288 www.progressivepractice.com
> Rochester, NY 14617
>
>
>
>

Hello Jon,

Thanks for the reply! Your more in depth solution helped me use your
solution posted on the web. It works like a charm now. Its all fast just
like you said :)

Much thanks.

Mike

-----BEGIN PGP SIGNATURE-----

iEYEARECAAYFAkuHUOwACgkQRAk1+p0GhSFd7ACfQdILgyC6gRYLP2lfK/n6VwWl
zrAAniABEmsS636nkujwrBVca/zMSFiZ
=PneT
-----END PGP SIGNATURE-----