Split daterange into sub periods

On 07/05/2018 06:49 AM, hmidi slim wrote:

Hi,
I'm looking for splitting a daterange into many subperiods following
this example:

Base Date: [2018-01-01, 2018-01-31]
overlapped_periods:
1- [ 2018-01-04, 2018-01-06]
2- [ 2018-01-09, 2018-01-12]
3- [ 2018-01-18, 2018-01-19]

Overlapping what?
They are not overlapping each other.

I try to get such a result:
1- [ 2018-01-01, 2018-01-03]
2- [ 2018-01-07, 2018-01-08]
3- [ 2018-01-13, 2018-01-17]
4- [ 2018-01-20, 2018-01-31]

Not sure what the above represents.
Are you looking for the dates in the range [2018-01-01, 2018-01-31] that
are not in the 3 sub-ranges at the top of the post?

The operator '-' does not support this :

SELECT daterange('2018-01-01', '2018-01-31', '[]') -
daterange('2018-01-04', '2018-01-06', '[]');

I got this error:

*ERROR: result of range difference would not be contiguous

That is expected:

https://www.postgresql.org/docs/10/static/functions-range.html

"The union and difference operators will fail if the resulting range
would need to contain two disjoint sub-ranges, as such a range cannot be
represented."

*

Is there any operators to make the split of daterange?*
*

Not that I know of.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thursday, July 5, 2018, hmidi slim <hmidi.slim2@gmail.com> wrote:

I got this error:

*ERROR: result of range difference would not be contiguous*

Is there any operators to make the split of daterang

To refine what Adrian said, operators cannot return a setof result so this

is basically impossible. The interface for such a behavior would have to
be a function. You one someone already wrote elsewhere or you can write
your own. There isn't one in core that I see.

David J.

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].

On Thu, Jul 5, 2018 at 4:16 PM, 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].

Your example maybe fine to illustrate a definition, but it is totally
inadequate to describe what you want.

You'll, at least, need to refine your question. Something like "I have
a big period and a set of small periods, and want to calculate the set
of subperiods of the big one not covered by the small ones", and also
some conditions ( i.e., can the small periods interesect? are they
completely covered by the big one? can they include the endpoints of
the big one? Can they be empty? Does the result need to be minimal (
in the number of result periods ) ? ).

Even then, this is not trivial and, as said above, you will probably
need a function for it.

Francisco Olarte.

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)

El jue., 5 de jul. de 2018 a la(s) 10:39, Andreas Kretschmer (
andreas@a-kretschmer.de) escribió:

On 05.07.2018 15:49, hmidi slim wrote:

Hi,
I'm looking for splitting a daterange into many subperiods following this
example:

Base Date: [2018-01-01, 2018-01-31]
overlapped_periods:
1- [ 2018-01-04, 2018-01-06]
2- [ 2018-01-09, 2018-01-12]
3- [ 2018-01-18, 2018-01-19]

I try to get such a result:
1- [ 2018-01-01, 2018-01-03]
2- [ 2018-01-07, 2018-01-08]
3- [ 2018-01-13, 2018-01-17]
4- [ 2018-01-20, 2018-01-31]

The operator '-' does not support this :

SELECT daterange('2018-01-01', '2018-01-31', '[]') - daterange('2018-01-04', '2018-01-06', '[]');

I got this error:

*ERROR: result of range difference would not be contiguous

*

Is there any operators to make the split of daterange?

andreas@[local]:5432/test# \d hmidi
Table "public.hmidi"
Column | Type | Collation | Nullable | Default
--------+-----------+-----------+----------+---------
id | integer | | not null |
d | daterange | | |
Indexes:
"hmidi_pkey" PRIMARY KEY, btree (id)

andreas@[local]:5432/test# insert into hmidi values
(1,'[2018-01-04,2018-01-06]');INSERT 0 1
andreas@[local]:5432/test# insert into hmidi values
(2,'[2018-01-09,2018-01-12]');INSERT 0 1
andreas@[local]:5432/test# insert into hmidi values
(3,'[2018-01-18,2018-01-19]');INSERT 0 1
andreas@[local]:5432/test# with month as (select s::date from
generate_series('2018-01-01'::date, '2018-01-31'::date,'1day'::interval)
s), tmp as ( select month.s, case when hmidi.d @> month.s then 1 else NULL
end as covered from month left join hmidi on month.s <@ hmidi.d),tmp2 as (
select *, coalesce((sum(case when covered = 1 then 1 else NULL end) over
(order by s))+1,1) as p from tmp) select p, min(s), max(s) from tmp2 where
covered is null group by p order by p;
p | min | max
----+------------+------------
1 | 2018-01-01 | 2018-01-03
4 | 2018-01-07 | 2018-01-08
8 | 2018-01-13 | 2018-01-17
10 | 2018-01-20 | 2018-01-31
(4 rows)

Regards, Andreas
--
2ndQuadrant Deutschland

--
Cordialmente,

Ing. Hellmuth I. Vargas S.
Esp. Telemática y Negocios por Internet
Oracle Database 10g Administrator Certified Associate
EnterpriseDB Certified PostgreSQL 9.3 Associate

On 05.07.2018 15:49, hmidi slim wrote:

Hi,
I'm looking for splitting a daterange into many subperiods following
this example:

Base Date: [2018-01-01, 2018-01-31]
overlapped_periods:
1- [ 2018-01-04, 2018-01-06]
2- [ 2018-01-09, 2018-01-12]
3- [ 2018-01-18, 2018-01-19]

I try to get such a result:
1- [ 2018-01-01, 2018-01-03]
2- [ 2018-01-07, 2018-01-08]
3- [ 2018-01-13, 2018-01-17]
4- [ 2018-01-20, 2018-01-31]

The operator '-' does not support this :
SELECT daterange('2018-01-01', '2018-01-31', '[]') -
daterange('2018-01-04', '2018-01-06', '[]');
I got this error:
*ERROR: result of range difference would not be contiguous *
Is there any operators to make the split of daterange?

andreas@[local]:5432/test# \d hmidi
                Table "public.hmidi"
 Column |   Type    | Collation | Nullable | Default
--------+-----------+-----------+----------+---------
 id     | integer   |           | not null |
 d      | daterange |           |          |
Indexes:
    "hmidi_pkey" PRIMARY KEY, btree (id)

andreas@[local]:5432/test# insert into hmidi values
(1,'[2018-01-04,2018-01-06]');INSERT 0 1
andreas@[local]:5432/test# insert into hmidi values
(2,'[2018-01-09,2018-01-12]');INSERT 0 1
andreas@[local]:5432/test# insert into hmidi values
(3,'[2018-01-18,2018-01-19]');INSERT 0 1
andreas@[local]:5432/test# with month as (select s::date from
generate_series('2018-01-01'::date, '2018-01-31'::date,'1day'::interval)
s), tmp as ( select month.s, case when hmidi.d @> month.s then 1 else
NULL end as covered from month left join hmidi on month.s <@
hmidi.d),tmp2 as ( select *, coalesce((sum(case when covered = 1 then 1
else NULL end) over (order by s))+1,1) as p from tmp) select p, min(s),
max(s) from tmp2 where covered is null group by p order by p;
 p  |    min     |    max
----+------------+------------
  1 | 2018-01-01 | 2018-01-03
  4 | 2018-01-07 | 2018-01-08
  8 | 2018-01-13 | 2018-01-17
 10 | 2018-01-20 | 2018-01-31
(4 rows)

Regards, Andreas
--
2ndQuadrant Deutschland

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

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.

Based on you example I updated it to get the results that I want:
create table hmidi(
id serial primary key,
product_id integer,
d date range)

insert into hmidi(product_id, d) values(15, '[2018-11-01, 2018-11-01]');
insert into hmidi(product_id, d) values(15, '[2018-11-03, 2018-11-04]');

Then I update you query:
with month as (
select distinct s::date, hmidi.product_id
from generate_series('2018-11-01'::date,
'2018-11-05'::date,'1day'::interval) s
cross join hmidi
order by s::date
),
tmp as (
select month.s, month.product_id,
case when (hmidi.d @> month.s)
then 1
else null
end as covered
from month inner join hmidi on hmidi.product_id = month.product_id
group by month.product_id, month.s, hmidi.d, hmidi.product_id
),
tmp2 as (
select *,
coalesce((sum(case when covered = 1 then 1 else NULL end) over
(partition by product_id order by s)) + 1,1) as p
from tmp
group by product_id,s, covered
)
select product_id,
daterange(min(s), max(s)) as range
from tmp2
where covered is null
and product_id = 15
group by p, product_id

I got these results:
15 "[2018-11-01,2018-11-02)"
15 "empty"
15 "[2018-11-04,2018-11-05)"

However I should get:

15 "[2018-11-02, 2018-11-02]"
15 "[2018-11-05, 2018-11-05]"

I didn't master the usage of window functions such as 'over and partition'.
I tried to resolve the problems by myself but no vain. Could you try to
clarify me what is wrong with my query?
Thanks.

2018-07-05 16:39 GMT+01:00 Andreas Kretschmer <andreas@a-kretschmer.de>: