On Sat, Oct 25, 2014 at 5:00 AM, <hari.fu...@gmail.com> wrote: > John McKown <john.archie.mck...@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 >
Perfect! Thanks! Now I just need to understand how that works.. :) Joe
