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.

The part about joining multiple touching dateranges to give a single
continuous daterange is what Richard Snodgrass calls "coalescing" in
*Developing Time-Oriented Database Applications in SQL*, pages 159 -
169, available printed or as a free PDF at
http://www2.cs.arizona.edu/~rts/publications.html (His approach also
supports overlapping ranges, but it sounds like you don't need that.) If
you had a coalesced view (or maybe a set-returning function), you could
do this:

SELECT term
FROM coalesced_staff_assign
WHERE client_id = 5
AND term @> '2018-15-01'
;

I can't think of any way to avoid scanning all of a given client's
records, but hopefully client_id alone would be selective enough to
still give you good performance.

Oh also: in reading Snodgrass's SQL, note that he assumes closed-open
ranges (i.e. '[)'), so you'll need to adjust some things to fit with
your closed-closed ranges (or always use `staff_assign_date_end -
INTERVAL '1 day'` if every assignment is at least 1 day long). On the
other hand with built-in range types you might be able to simplify his
pure-SQL solutions.

--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com