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(at)rodonnell(dot)ie