Re: Given a set of daterange, finding the continuous range that includes 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(dot)tanzer(at)agency-software(dot)org <mailto:ken(dot)tanzer(at)agency-software(dot)org>
> (253) 245-3801
>
> Subscribe to the mailing list
> <mailto:agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com