Thread: Finding date intersections

Finding date intersections

From

Joe Van Dyk

Date:

24 October 2014, 02:11:50

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

Re: Finding date intersections

From

John McKown

Date:

24 October 2014, 15:18:26

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

Re: Finding date intersections

From

David G Johnston

Date:

24 October 2014, 18:03:04

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.

Re: Finding date intersections

From

Joe Van Dyk

Date:

24 October 2014, 20:08:26

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.

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

Re: Finding date intersections

From

hari.fuchs@gmail.com

Date:

25 October 2014, 12:00:36

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

Re: Finding date intersections

From

Joe Van Dyk

Date:

25 October 2014, 20:30:13

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