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