Re: Overlapping timestamptz ranges with priority

On 03/07/2021 18:59, Adrian Klaver wrote:

> I'm not sure this is doing what you think it is;

[...]

>  select * from get_visible_bookings();
>  booking_id | aircraft_reg | type_code | booking_time                     | owner_uid | owner_name
> ------------+--------------+-----------+-----------------------------------------------------+-----------+------------
>
>           1 | A1ZX         | type1     | ["2021-07-03 10:00:00-07","2021-07-03 14:00:00-07"] | 1         | aklaver
>           2 | A1ZX         | type1     | ("2021-07-03 14:00:00-07","2021-07-03 16:00:00-07"] | 1         | aklaver
>           3 | A1ZX         | type1     | ["2021-07-04 09:00:00-07","2021-07-04 12:00:00-07"] | 1         | aklaver
>           4 | B2CA         | type2     | ["2021-07-03 09:00:00-07","2021-07-03 10:00:00-07") | 2         | wilbur
> (4 rows)
>
>
> The booking_id for aircraft B2CA with booking_time of  ["2021-07-03
> 11:00:00-07","2021-07-03 14:00:00-07"] is not accounted for. There is a
> step missing that accounts for bookings being assigned to a particular
> aircraft.

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;

When this is corrected, I get what I'm looking for (trying it here with
your data):

set time zone 'America/Los_Angeles';
SET

select booking_id, aircraft_reg, booking_time from bookings order by
aircraft_reg, lower(booking_time);

booking_id | aircraft_reg | booking_time

------------+--------------+-----------------------------------------------------
25 | A1ZX | ["2021-07-03 10:00:00-07","2021-07-03
14:00:00-07")
26 | A1ZX | ["2021-07-03 12:00:00-07","2021-07-03
16:00:00-07")
27 | A1ZX | ["2021-07-04 09:00:00-07","2021-07-04
12:00:00-07")
28 | B2CA | ["2021-07-03 09:00:00-07","2021-07-03
12:00:00-07"]
29 | B2CA | ["2021-07-03 11:00:00-07","2021-07-03
14:00:00-07"]
(5 rows)

select booking_id, aircraft_reg, booking_time from
get_visible_bookings() order by aircraft_reg, lower(booking_time);

booking_id | aircraft_reg | booking_time

------------+--------------+-----------------------------------------------------
25 | A1ZX | ["2021-07-03 10:00:00-07","2021-07-03
14:00:00-07")
26 | A1ZX | ["2021-07-03 14:00:00-07","2021-07-03
16:00:00-07")
27 | A1ZX | ["2021-07-04 09:00:00-07","2021-07-04
12:00:00-07")
28 | B2CA | ["2021-07-03 09:00:00-07","2021-07-03
12:00:00-07"]
29 | B2CA | ("2021-07-03 12:00:00-07","2021-07-03
14:00:00-07"]
(5 rows)

gfc_booking6_dev=# set time zone 'America/Los_Angeles';
SET
gfc_booking6_dev=# select booking_id, aircraft_reg, booking_time from
bookings order by aircraft_reg, lower(booking_time);
booking_id | aircraft_reg | booking_time

------------+--------------+-----------------------------------------------------
25 | A1ZX | ["2021-07-03 10:00:00-07","2021-07-03
14:00:00-07")
26 | A1ZX | ["2021-07-03 12:00:00-07","2021-07-03
16:00:00-07")
27 | A1ZX | ["2021-07-04 09:00:00-07","2021-07-04
12:00:00-07")
28 | B2CA | ["2021-07-03 09:00:00-07","2021-07-03
12:00:00-07"]
29 | B2CA | ["2021-07-03 11:00:00-07","2021-07-03
14:00:00-07"]
(5 rows)

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.

Thanks,

Ray.

--
Raymond O'Donnell // Galway // Ireland
ray(at)rodonnell(dot)ie