Обсуждение: Split daterange into sub periods
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]
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?
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-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:andreas@[local]:5432/test# \d hmidiHi,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?
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
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:
andreas@[local]:5432/test# \d hmidiHi,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?
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]');
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
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)"
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>:
andreas@[local]:5432/test# \d hmidi
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?
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