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.
