Skip site navigation (1) Skip section navigation (2)

Re: Finding overlapping records

From: Jon Erdman <postgresql(at)thewickedtribe(dot)net>
To: Frank Sheiness <frank(at)dough(dot)net>
Cc: austinpug(at)postgresql(dot)org
Subject: Re: Finding overlapping records
Date: 2009-12-10 17:15:38
Message-ID: 4B212CBA.9010602@thewickedtribe.net (view raw or flat)
Thread:
Lists: austinpug
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Doh. Just read your whole message, so I see why you were only checking
for the one case. I think the full discussion is still worthwhile though.

Jon Erdman wrote:
> 
> Frank,
> 
> First of all, you've got a big hole in your overlap check. You're only
> checking for (new span is --- existing is +++):
> 
>     *+++++++*
>        *-------*
> 
> when you really need to check for:
> 
>    *++++++++*
>      *---------*
> *-------*
> *--------------*
>      *---*
> 
> /me pulls out Celko's SQL For Smarties...
> 
> So what you would naturally write is perhaps (s1 and e1 are start and
> end of existing span, s2 and e2 are the new span):
> 
> WHERE
>    s2 between s1 and e1
> OR e2 between s1 and e1
> OR s1 between s2 and e2
> OR e1 between s2 and e2;
> 
> which is a bit long and ugly. There's a shortcut you can take, here's
> how you would search for things that *don't* overlap:
> 
>          *+++++*
> *----*
>                   *-----*
> 
> so you can write it as:
> 
> WHERE NOT ((e2 < s1) OR (s2 > e1));
> 
> which is *much* cleaner, no? ;)
> 
> Credit goes to Joe Celko, SQL for Smarties, Chapter 13: Between and
> Overlaps Predicate, 13.2 Overlaps Predicate, page 279.
> 
> Postgres actually has OVERLAPS, so you can just say:
> 
> WHERE (s2, e2) OVERLAPS (s1, e1);
> 
> however, at least in 8.1, that doesn't use the indexes on the start_date
> and end_date. The shortcut above does use those indexes and is nice and
> fast.
> 
> You should test and see if 8.3 or 8.4 will use the indexes for OVERLAPS
> though...

- --

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


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

iEYEARECAAYFAkshLLoACgkQRAk1+p0GhSFVoQCePh1qJeljm6M294ItqKmO36a9
mvoAn2qo1uzd0keVZe8XfH6Zg5DI6XS1
=0/8a
-----END PGP SIGNATURE-----

In response to

Responses

austinpug by date

Next:From: Frank SheinessDate: 2009-12-10 20:42:56
Subject: Re: Finding overlapping records
Previous:From: Jon ErdmanDate: 2009-12-10 17:12:32
Subject: Re: Finding overlapping records

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group