John McKown <john.archie.mckown@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
