From: | Andreas Kretschmer <andreas(at)a-kretschmer(dot)de> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Split daterange into sub periods |
Date: | 2018-07-05 15:39:26 |
Message-ID: | 4fafc5e2-eba4-61c9-c6d0-9f785be4366e@a-kretschmer.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)[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(at)[local]:5432/test# insert into hmidi values
(1,'[2018-01-04,2018-01-06]');INSERT 0 1
andreas(at)[local]:5432/test# insert into hmidi values
(2,'[2018-01-09,2018-01-12]');INSERT 0 1
andreas(at)[local]:5432/test# insert into hmidi values
(3,'[2018-01-18,2018-01-19]');INSERT 0 1
andreas(at)[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
From | Date | Subject | |
---|---|---|---|
Next Message | Igor Korot | 2018-07-05 15:40:51 | Re: How to watch for schema changes |
Previous Message | Igor Korot | 2018-07-05 15:37:34 | Re: How to watch for schema changes |