Re: Split daterange into sub periods

[hmidi slim <hmidi.slim2@xxxxxxxxx>]

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@xxxxxxxxxxxxxxx>:

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