Finding date intersections

On Thu, Oct 23, 2014 at 9:10 PM, Joe Van Dyk <joe@tanga.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

John McKown wrote

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')

I presume the second output row should be [5,6)...

And why are you using a timestamp range when your data are dates?

My first thought is to explode the ranges into distinct dates, order them
inside a window, use lag(...) to find breaks,p and assign groups, the for
each group take the min and max of the group and form a new range. Not sure
exactly what the SQL looks like - especially the range explosion - but
should technically work even though performance may suck. Probably want to
use lateral and generate_series(...) if you are on a more recent version.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Finding-date-intersections-tp5824102p5824194.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Oct 24, 2014 at 11:02 AM, David G Johnston <
david.g.johnston@gmail.com> wrote:

John McKown wrote

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')

I presume the second output row should be [5,6)...

Yes, sorry. And I suppose the third argument to each of those should be
'[)'.

And why are you using a timestamp range when your data are dates?

Didn't want to type our the hours, my my real situation involves
timestamptz's.

My first thought is to explode the ranges into distinct dates, order them
inside a window, use lag(...) to find breaks,p and assign groups, the for
each group take the min and max of the group and form a new range. Not
sure
exactly what the SQL looks like - especially the range explosion - but
should technically work even though performance may suck. Probably want to
use lateral and generate_series(...) if you are on a more recent version.

Thanks! I'll look into this.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, Oct 25, 2014 at 5:00 AM, <hari.fuchs@gmail.com> wrote:

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

Perfect! Thanks! Now I just need to understand how that works.. :)

Joe