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

On 02/22/2018 04:58 PM, Ken Tanzer wrote:

On Thu, Feb 22, 2018 at 4:53 PM, Adrian Klaver <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

    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
    <https://www.postgresql.org/docs/10/static/functions-range.html>

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


Thanks Adrian.  But how would you apply that to this situation, where I have a series of (quite possibly discontinuous) dateranges?

This is going to depend on a more formal definition of the problem with some sample data. Right now I am trying to reconcile "what period were they continuously in the program that includes X date?" with "where I have a series of (quite possibly discontinuous) dateranges? " Maybe its just me,  I can't see how discontinuous can also be continuously.