Re: Finding date intersections - Mailing list pgsql-general

John McKown <john.archie.mckown@gmail.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