From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | hmidi slim <hmidi(dot)slim2(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Split daterange into sub periods |
Date: | 2018-07-05 14:03:31 |
Message-ID: | 21b62dd6-fd29-b0da-8b0d-89c04685d97e@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2018-07-05 14:13:18 | Split daterange into sub periods |
Previous Message | Melvin Davidson | 2018-07-05 14:01:41 | Re: Cloning schemas |