Split daterange into sub periods

From: hmidi slim <hmidi(dot)slim2(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Split daterange into sub periods
Date: 2018-07-05 13:49:17
Message-ID: CAMsqVxsSiN_-Tx3tPzAammUyQNBVOKY3cpHfdGFb8wVX7oK3+w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Melvin Davidson 2018-07-05 14:01:41 Re: Cloning schemas
Previous Message David G. Johnston 2018-07-05 13:18:11 Re: FK v.s unique indexes