Re: Overlapping timestamptz ranges with priority

On 7/3/21 12:16 PM, Ray O'Donnell wrote:
> On 03/07/2021 18:59, Adrian Klaver wrote:
>

>> 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;

Was the above supposed to show the change?

>
> 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);
>

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."

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

insert into bookings(aircraft_reg, type_code, booking_time, owner_uid,
owner_name) values ('A1ZX', 'type1', '[07/04/2021 09:00, 07/04/2021
14:00]', '1', 'aklaver');

insert into bookings(aircraft_reg, type_code, booking_time, owner_uid,
owner_name) values ('A1ZX', 'type1', '[07/04/2021 09:00, 07/04/2021
11:00]', '1', 'aklaver');

select * from bookings order by aircraft_reg, lower(booking_time);
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
12:00:00-07","2021-07-03 16:00:00-07"] | 1 | aklaver
6 | A1ZX | type1 | ["2021-07-04
09:00:00-07","2021-07-04 14:00:00-07"] | 1 | aklaver
3 | A1ZX | type1 | ["2021-07-04
09:00:00-07","2021-07-04 12:00:00-07"] | 1 | aklaver
7 | A1ZX | type1 | ["2021-07-04
09:00:00-07","2021-07-04 11:00:00-07"] | 1 | aklaver
4 | B2CA | type2 | ["2021-07-03
09:00:00-07","2021-07-03 12:00:00-07"] | 2 | wilbur
5 | B2CA | type2 | ["2021-07-03
11:00:00-07","2021-07-03 14:00:00-07"] | 2 | wilbur

select * from bookings order by aircraft_reg, booking_time;
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
12:00:00-07","2021-07-03 16:00:00-07"] | 1 | aklaver
7 | A1ZX | type1 | ["2021-07-04
09:00:00-07","2021-07-04 11:00:00-07"] | 1 | aklaver
3 | A1ZX | type1 | ["2021-07-04
09:00:00-07","2021-07-04 12:00:00-07"] | 1 | aklaver
6 | A1ZX | type1 | ["2021-07-04
09:00:00-07","2021-07-04 14:00:00-07"] | 1 | aklaver
4 | B2CA | type2 | ["2021-07-03
09:00:00-07","2021-07-03 12:00:00-07"] | 2 | wilbur
5 | B2CA | type2 | ["2021-07-03
11:00:00-07","2021-07-03 14:00:00-07"] | 2 | wilbur

update bookings set type_code = 'type3' where type_code = 'type1';

select * from bookings order by aircraft_reg, booking_time;
booking_id | aircraft_reg | type_code |
booking_time | owner_uid | owner_name
------------+--------------+-----------+-----------------------------------------------------+-----------+------------
1 | A1ZX | type3 | ["2021-07-03
10:00:00-07","2021-07-03 14:00:00-07"] | 1 | aklaver
2 | A1ZX | type3 | ["2021-07-03
12:00:00-07","2021-07-03 16:00:00-07"] | 1 | aklaver
7 | A1ZX | type3 | ["2021-07-04
09:00:00-07","2021-07-04 11:00:00-07"] | 1 | aklaver
3 | A1ZX | type3 | ["2021-07-04
09:00:00-07","2021-07-04 12:00:00-07"] | 1 | aklaver
6 | A1ZX | type3 | ["2021-07-04
09:00:00-07","2021-07-04 14:00:00-07"] | 1 | aklaver
4 | B2CA | type2 | ["2021-07-03
09:00:00-07","2021-07-03 12:00:00-07"] | 2 | wilbur
5 | B2CA | type2 | ["2021-07-03
11:00:00-07","2021-07-03 14:00:00-07"] | 2 | wilbur

select * from bookings order by aircraft_reg, lower(booking_time);
booking_id | aircraft_reg | type_code |
booking_time | owner_uid | owner_name
------------+--------------+-----------+-----------------------------------------------------+-----------+------------
1 | A1ZX | type3 | ["2021-07-03
10:00:00-07","2021-07-03 14:00:00-07"] | 1 | aklaver
2 | A1ZX | type3 | ["2021-07-03
12:00:00-07","2021-07-03 16:00:00-07"] | 1 | aklaver
3 | A1ZX | type3 | ["2021-07-04
09:00:00-07","2021-07-04 12:00:00-07"] | 1 | aklaver
6 | A1ZX | type3 | ["2021-07-04
09:00:00-07","2021-07-04 14:00:00-07"] | 1 | aklaver
7 | A1ZX | type3 | ["2021-07-04
09:00:00-07","2021-07-04 11:00:00-07"] | 1 | aklaver
4 | B2CA | type2 | ["2021-07-03
09:00:00-07","2021-07-03 12:00:00-07"] | 2 | wilbur
5 | B2CA | type2 | ["2021-07-03
11:00:00-07","2021-07-03 14:00:00-07"] | 2 | wilbur

>
> 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,
>
> Ray.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com