Re: Overlapping timestamptz ranges with priority

On 03/07/2021 21:13, Adrian Klaver wrote:
> On 7/3/21 12:16 PM, Ray O'Donnell wrote:
>> Yes, you're right - I realised that after I sent my last email. The 
>> inner loop in the function should have matched overlapping bookings by 
>> aircraft registration:
>>
>>      -- For each booking, check whether there are any with
>>      -- a higher priority and whose times overlap it.
>>      for m_overlapping in
>>        select booking_id, booking_time from bookings
>>        where booking_id < m_rec.booking_id
>>        and booking_time && m_rec.booking_time
>>      loop
>>        -- Snip away any overlapping (obscured) time.
>>        m_visible_time := m_visible_time - m_overlapping.booking_time;
>>      end loop;
> 
> Was the above supposed to show the change?

Whoops, sorry, here it is:

     for m_overlapping_time in
         select booking_id, booking_time from bookings
         where aircraft_reg = m_rec.aircraft_reg
           and booking_id < m_rec.booking_id
           and booking_time && m_rec.booking_time
     loop
         [... etc ...]

>> select booking_id, aircraft_reg, booking_time from bookings order by 
>> aircraft_reg, lower(booking_time);
>>
> 
> Pretty sure lower() is not needed, if I'm following this correctly:
> 
> https://www.postgresql.org/docs/12/functions-range.html
> 
> "The simple comparison operators <, >, <=, and >= compare the lower 
> bounds first, and only if those are equal, compare the upper bounds. 
> These comparisons are not usually very useful for ranges, but are 
> provided to allow B-tree indexes to be constructed on ranges."

Ah, good - thanks for pointing that out.

> In the case where the lower bound  is the same I'm thinking using 
> lower() will result in different ordering under different circumstances:

I see what you mean. It shouldn't matter for our use case; ordering on 
the aircraft registration and time is what counts for us, and the output 
of the function ought to produce well-ordered booking times for each 
aircraft. The other columns are used for display purposes only.

>> I need to play with it a bit more: for example, if a long, 
>> lower-priority booking is behind a short, higher-priority one such 
>> that the long one extends both before and after the short one, then 
>> the range-difference operator will give me an error about a 
>> non-contiguous result. However, I think I'm heading in the right 
>> direction now.
> 
> Great. Good luck going forward.

Thanks again for your help - much appreciated!

Ray.


-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie