Re: Finding date intersections

On Thu, Oct 23, 2014 at 9:10 PM, Joe Van Dyk <joe(at)tanga(dot)com> wrote:

> I have a table of sales that have possibly overlapping time ranges. I want
> to find all the timeranges where there's an active sale. How would you do
> that?
>
> create table sales (
> times tstzrange
> );
>
> insert into sales values
> (tstzrange('2014-1-1', '2014-1-2')),
> (tstzrange('2014-1-2', '2014-1-3')),
> (tstzrange('2014-1-2', '2014-1-4')),
> (tstzrange('2014-1-5', '2014-1-6'));
>
> -- want back:
> -- tstzrange('2014-1-1', '2014-1-4')
> -- tstzrange('2014-1-6', '2014-1-6')
>
> Thanks,
> Joe
>

​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​

--
The temperature of the aqueous content of an unremittingly ogled
culinary vessel will not achieve 100 degrees on the Celsius scale.

Maranatha! <><
John McKown