Re: Given a set of daterange, finding the continuous range thatincludes a particular date

On 02/22/2018 04:44 PM, Ken Tanzer 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 array_agg(daterange(staff_assign_date,staff_assign_date_end,'[]')
> ) 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 efficiently.

The operator I use to solve similar problems:

https://www.postgresql.org/docs/10/static/functions-range.html

@>     contains element     '[2011-01-01,2011-03-01)'::tsrange @> 
'2011-01-10'::timestamp     t


> 
> Any advice or suggestions or ways to go about this appreciated.  Thanks!
> 
> Ken
> 
> 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 performance.
> 
> 
> -- 
> AGENCY Software
> A Free Software data system
> By and for non-profits
> /http://agency-software.org//
> /https://demo.agency-software.org/client/
> ken.tanzer@agency-software.org <mailto:ken.tanzer@agency-software.org>
> (253) 245-3801
> 
> Subscribe to the mailing list 
> <mailto:agency-general-request@lists.sourceforge.net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.


-- 
Adrian Klaver
adrian.klaver@aklaver.com