Обсуждение: 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

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,

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.