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.

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@illuminatedcomputing.com