Re: Split daterange into sub periods

On 5 July 2018 at 16:16, hmidi slim <hmidi.slim2@gmail.com> wrote:
> In fact I'm trying to split a period in sub periods. Following this example
> :
> If I have a period =[2018-01-01, 2018-01-31] and two other periods
> [2018-01-04, 2018-01-06] and [2018-01-08, 2018-01-08].
> If I split the base period '[2018-01-01, 2018-01-31]' by the other two
> periods '[2018-01-04, 2018-01-06]' and '[2018-01-08, 2018-01-08]' I will got
> such a result:
> [2018-01-01, 2018-01-03]
> [2018-01-07, 2018-01-07]
> [2018-01-09, 2018-01-31].

What about a recursive CTE?

What about a recursive CTE?

with recursive
    period as (select '[2018-01-01, 2018-01-31]'::daterange as range)
,    exclude as (
        select range
          from (values
              ('[2018-01-01, 2018-01-03]'::daterange)
        ,    ('[2018-01-07, 2018-01-07]'::daterange)
        ,    ('[2018-01-09, 2018-01-31]'::daterange)
          ) v(range)
    )
,    available (lo, hi, exclude, available) as (
        select
            lower(p.range), upper(p.range)
        ,    x.range
        ,    p.range - x.range
          from period p,exclude x
          where not exists (
              select 1
              from exclude x2
             where lower(x2.range) < lower(x.range)
               and lower(x2.range) >= lower(p.range)
          )

         union all

         select
            upper(x.range), hi
        ,    x.range
        ,    daterange(upper(x.range), hi)
          from available a, exclude x
         where a.lo <= a.hi
           and lower(x.range) > lo
           and not exists (
            select 1
              from exclude x2
             where lower(x2.range) < lower(x.range)
               and lower(x2.range) > lo
         )
    )
select * from available;

     lo     |     hi     |         exclude         |        available
------------+------------+-------------------------+-------------------------
 2018-01-01 | 2018-02-01 | [2018-01-01,2018-01-04) | [2018-01-04,2018-02-01)
 2018-01-08 | 2018-02-01 | [2018-01-07,2018-01-08) | [2018-01-08,2018-02-01)
 2018-02-01 | 2018-02-01 | [2018-01-09,2018-02-01) | empty
(3 rows)

It can probably be optimized a bit, I haven't played with ranges much yet.

Regards,
Alban Hertroys
-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.