Re: Overlapping timestamptz ranges with priority - Mailing list pgsql-general

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@rodonnell.ie