This answer is perhaps useful if I understand your problem correctly. But I 
might have interpreted it wrongly. :-)

I would probably start with merging intervals so that overlapping and adjacent 
intervals become single continuous intervals, then select from those merged 

We have an application with a lot of interval handling in PostgreSQL, and we 
use many variants of algorithms based on one by Itzik Ben Gan which he calls 
“packing intervals”. The post we started with was an old reader’s challenge 
from SQL Magazine. It has since been updated when MS-SQL started supporting 
window functions better (Itzik Ben Gan is a MS-SQL-guy).

Basically, it is a few CTE:s which convert the intervals into “start” (+1) and 
“stop” (-1) events, then keeps a running sum of these, and finally creates new 
intervals by noting that the merged (or “packed”) intervals starts with events 
that had sum=0 before them and stops with events which have sum=0 after them.

It involves both CTE:s and window functions and is quite a beautiful example of 

I think it’s best to google it, he does a good job of explaining how it works.

Hope that helps a bit at least.


On 23 Feb 2018, at 01:44, Ken Tanzer 
<ken.tan...@gmail.com<mailto:ken.tan...@gmail.com>> wrote:

Hi, hoping to get some help with this.  I'm needing to take a specific date, a 
series of dateranges  and, given a specific date, return a single conitinuous 
daterange that includes that date.

To elaborate a bit, I've got lots of tables that include start and end dates.  
For example:

CREATE TABLE tbl_staff_assign (
    staff_assign_id         SERIAL PRIMARY KEY,
    client_id               INTEGER NOT NULL REFERENCES tbl_client (client_id),
    staff_id                INTEGER REFERENCES tbl_staff(staff_id),
    staff_assign_type_code      VARCHAR(10) NOT NULL REFERENCES 
tbl_l_staff_assign_type (staff_assign_type_code),
    staff_assign_date           DATE NOT NULL,
    staff_assign_date_end       DATE,

So a client might leave a progrma and then return later, or they might simply 
switch to another staff_id.  (In which case one record will have and end date, 
and the next record will start on the next day.)  In this case I need to know 
"what period were they continuously in the program that includes X date?"  So 
I'd like to be able to do something like:

"SELECT staff_assign_date,continuous_daterange( staff_assign_date, (SELECT 
) FROM staff_assign sa2 WHERE sa2.client_id=sa.client_id) FROM staff_assign sa

I've done this before with procedures specific to a particular table, and 
working with the start and end dates.  I'm now wanting to try to do this once 
generically that will work for all my cases.  So I'm hoping to do this in a way 
that performance isn't horrible.  And it's a little unclear to me how much and 
how I might be able to use the daterange operators to accomplish this 

Any advice or suggestions or ways to go about this appreciated.  Thanks!


p.s.,  Another small wrinkle is these records aren't always perfect, and 
ideally I'd allow for an optional fudge factor that would allow small gaps to 
be ignored.  I could just add that in every query (start_date+2,end_date-2), 
but it might be nice to have the function do it, if it didn't badly hurt 

AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801

Subscribe to the mailing 
list<mailto:agency-general-requ...@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

Reply via email to