Re: Overlapping ranges

On 19 Jun 2014, at 1:47, Jason Long <mailing(dot)lists(at)octgsoftware(dot)com> wrote:

> I have a large table of access logs to an application.
>
> I want is to find all rows that overlap startdate and enddate with any
> other rows.
>
> The query below seems to work, but does not finish unless I specify a
> single id.
>
> select distinct a1.id
> from t_access a1,
> t_access a2
> where tstzrange(a1.startdate, a1.enddate) &&
> tstzrange(a2.startdate, a2.enddate)

You’re comparing overlapping records twice there; you compare all records in a1 to all records in a2. You’ll want to skip the records that you already compared.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.