Re: Split daterange into sub periods
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 :
>
>
> 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
>
Re: Split daterange into sub periods
On 5 July 2018 at 16:16, hmidi slim 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.
Re: Split daterange into sub periods
Hi
select ($$[$$|| to_char(min(n.dato),'-MM-DD') || $$,$$ ||
to_char(max(n.dato),'-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 (
andr...@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
Re: Split daterange into sub periods
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
Re: Split daterange into sub periods
On Thu, Jul 5, 2018 at 4:16 PM, hmidi slim 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.
Re: Split daterange into sub periods
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].
Split daterange into sub periods
On Thursday, July 5, 2018, hmidi slim 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.
Re: 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.kla...@aklaver.com
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]
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?
