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 <andr...@a-kretschmer.de>:
>
>
> 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
>
