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.klaver@xxxxxxxxxxx
