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

Re: Finding overlapping records

From: Frank Sheiness <frank(at)korcett(dot)com>
To: austinpug(at)postgresql(dot)org
Cc: Jon Erdman <postgresql(at)thewickedtribe(dot)net>
Subject: Re: Finding overlapping records
Date: 2009-12-10 20:42:56
Message-ID: 20091210204256.GA44979@forbidden.texas.rr.com (view raw or flat)
Thread:
Lists: austinpug
I think you are right about the other cases.  Especially about the case
where the newer lease resides entirely inside the older lease.  I noticed
that one right after I sent the email last night and updated my trigger for
it.

As for the other ones, I was just depending on the order in the lease file
to protect me.

We're still on 8.2 for now.  I started to look at the period data type from
John Davis and will play with it today.

On Thu, Dec 10, 2009 at 11:15:38AM -0600, Jon Erdman wrote:
> -----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: Jon ErdmanDate: 2009-12-10 20:52:08
Subject: Re: Finding overlapping records
Previous:From: Jon ErdmanDate: 2009-12-10 17:15:38
Subject: Re: Finding overlapping records

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