Re: Finding date intersections

On Sat, Oct 25, 2014 at 5:00 AM, <hari(dot)fuchs(at)gmail(dot)com> wrote:

> John McKown <john(dot)archie(dot)mckown(at)gmail(dot)com> writes:
>
> > ​I've been think about this for a bit. But I'm not getting a real
> solution.
> > I have an approach, shown below, that I think might be the bare
> beginnings
> > of an approach, but I'm just not getting any more inspiration. Perhaps it
> > will spark an idea for you or someone else.
> >
> > with recursive explode(times) as (
> > select * from sales
> > union
> > select tstzrange(lower(a.times)::timestamptz,upper(b.times)::timestamptz)
> > as times
> > from explode as a
> > join sales as b
> > on upper(a.times) = lower(b.times)
> > where lower(a.times) is not null and upper(b.times) is not null
> > )
> > select * from explode
> > order by times
> > ;
> >
> > If you run it with your example, you will see that it does get rows which
> > contain the answer. But it gets all the intermediate rows as well. It is
> > removing those "intermediate result" rows that I just can't get a handle
> > onl​
>
> For that, you could use a LEFT JOIN with itself:
>
> WITH RECURSIVE explode(times) AS (
> SELECT times
> FROM sales
> UNION
> SELECT a.times + b.times
> FROM explode a
> JOIN sales b ON b.times && a.times OR b.times -|- a.times
> )
> SELECT a.times
> FROM explode a
> LEFT JOIN explode b ON b.times @> a.times AND b.times != a.times
> WHERE b.times IS NULL
> ORDER BY a.times
>

Perfect! Thanks! Now I just need to understand how that works.. :)

Joe