From: | Alban Hertroys <haramrae(at)gmail(dot)com> |
---|---|
To: | hmidi slim <hmidi(dot)slim2(at)gmail(dot)com> |
Cc: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Split daterange into sub periods |
Date: | 2018-07-06 09:59:27 |
Message-ID: | CAF-3MvOigJhCgHczUAC3rpKhUGMDfGNJ2TkOjFB4DhyhTsyi_g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 5 July 2018 at 16:16, hmidi slim <hmidi(dot)slim2(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Moreno Andreo | 2018-07-06 14:01:04 | Role problem in Windows |
Previous Message | Pavel Stehule | 2018-07-06 09:57:47 | Re: How to remove elements from array . |