Re: Overlapping timestamptz ranges with priority - Mailing list pgsql-general
| From | Adrian Klaver |
|---|---|
| Subject | Re: Overlapping timestamptz ranges with priority |
| Date | |
| Msg-id | 410efc20-181a-5af6-1a87-20ee13fae14a@aklaver.com Whole thread Raw |
| In response to | Re: Overlapping timestamptz ranges with priority (Ray O'Donnell <ray@rodonnell.ie>) |
| Responses |
Re: Overlapping timestamptz ranges with priority
(Ray O'Donnell <ray@rodonnell.ie>)
|
| List | pgsql-general |
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@aklaver.com
pgsql-general by date: