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
