Re: Split daterange into sub periods

On 07/05/2018 08:30 AM, Hellmuth Vargas wrote:
> 
> Hi
> 
> select ($$[$$|| to_char(min(n.dato),'YYYY-MM-DD') || $$,$$ || 
> to_char(max(n.dato),'YYYY-MM-DD') || $$]$$)::daterange,    
> daterange(min(n.dato)::date,max(n.dato)::date)
> from (
> select u.dato,anterior,(u.dato-anterior)::interval,sum(case when 
> anterior is null or (u.dato -anterior)::interval='1 day'::interval  then 
> 0 else 1 end) over(order by u.dato) as grupo
> from (
> select u.dato, lag(u.dato) over( order by u.dato) as anterior, 
> lead(u.dato) over( order by u.dato)
> from (
> select * from generate_series(lower('[2018-01-01, 
> 2018-01-31]'::daterange),upper('[2018-01-01, 2018-01-31]'::daterange),'1 
> day'::interval) as a(dato)
> except
> (
> select generate_series(lower(a.dato),upper(a.dato),'1 day'::interval) 
> from 
>
(values('[2018-01-04,2018-01-06]'::daterange),('[2018-01-09,2018-01-12]'::daterange),('[2018-01-18,2018-01-19]'::daterange))

> as a(dato)
> 
> )
> ) as u order by u.dato
> ) as u
> ) as n
> group by grupo
> order by 1
> 
> 
> 
>          daterange        |        daterange
> -------------------------+-------------------------
>   [2018-01-01,2018-01-04) | [2018-01-01,2018-01-03)
>   [2018-01-08,2018-01-09) | empty
>   [2018-01-14,2018-01-18) | [2018-01-14,2018-01-17)
>   [2018-01-21,2018-02-02) | [2018-01-21,2018-02-01)
> (4 rows)
> 

Interesting but I am not sure this is working as the OP wants. If I am 
following the excluded ranges from your query are:

[2018-01-04,2018-01-06]
[2018-01-09,2018-01-12]
[2018-01-18,2018-01-19]

 From what I understand the OP wants, the returned periods should be:


[2018-01-01,2018-01-03]
[2018-01-07,2018-01-08]
[2018-01-13,2018-01-17]
[2018-01-20,2018-01-31]

-- 
Adrian Klaver
adrian.klaver@aklaver.com