Search Postgresql Archives

Re: Finding date intersections

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



John McKown <john.archie.mckown@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux