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

On 7/3/21 9:32 AM, Ray O'Donnell wrote:
> On 27/06/2021 23:41, Ray O'Donnell wrote:
>> Hi all,
>>
>> I'm playing with timestamptz ranges for a hobby project. I have a
>> table with a tstzrange column, in which the timestamps can overlap;
>> where they do, rows with a higher priority (derived from a bigint
>> primary key column) should be picked.
>>
>> What I'd like to do is present a view which shows timestamp ranges at
>>  the front of the queue, as it were; where ranges overlap, these may
>> be segments of a range from a particular row. I'm having trouble with
>> this and would appreciate suggestions.
> 
> I've come up with a way of doing it using a function... it's not going 
> to be very efficient if the number of rows gets large, due to nested 
> loops, but as the system generally keeps only a limited number of 
> bookings (no more that a few hundred), I think it'll do - certainly as a 
> first run at it.
> 
> Firstly, the table structure (as it now stands) on which the function 
> will operate:
> 
> CREATE TABLE bookings
> (
>      booking_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
>      aircraft_reg text NOT NULL,
>      type_code text NOT NULL,
>      booking_time tstzrange NOT NULL,
>      owner_uid text NOT NULL,
>      owner_name text NOT NULL,
> 
>      CONSTRAINT bookings_pk PRIMARY KEY (booking_id),
> 
>      (... foregin keys etc...)
> );
> 
> 
> And here's the function:
> 
> create or replace function get_visible_bookings()
> returns setof bookings
> language plpgsql
> as
> $$
> declare
>    m_rec bookings;
>    m_overlapping record;
>    m_visible_time tstzrange;
> begin
>    -- Loop through all bookings on the system, ordered on booking ID.
>    -- The booking ID also give the queue priority of the booking:
>    -- bookings with a lower ID have a higher priority.
>    for m_rec in
>      select * from bookings order by booking_id
>    loop
>      m_visible_time := m_rec.booking_time;
> 
>      -- 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;
> 
>      -- If any of the current booking's time is still visible,
>      -- then return the row with what's left of the time.
>      if not isempty(m_visible_time) then
>      return next row(m_rec.booking_id, m_rec.aircraft_reg,
>            m_rec.type_code, m_visible_time,
>            m_rec.owner_uid, m_rec.owner_name);
>      end if;
>    end loop;
> 
>    return;
> end;
> $$;

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

select * from bookings  order by  booking_id;
  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
           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 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 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.

> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com