Thread: Overlapping timestamptz ranges with priority

Overlapping timestamptz ranges with priority

From
Ray O'Donnell
Date:
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.

Here's a slightly simplified example:


create table bookings (
     booking_id bigint not null,
     booking_time tstzrange not null,

     constraint bookings_pk primary key (booking_id)
);

insert into bookings (booking_id, booking_time) values
(1, tstzrange('2021-06-20 12:00+01', '2021-06-20 14:00+01', '[)')),
(2, tstzrange('2021-06-20 13:00+01', '2021-06-20 16:00+01', '[)'));


And what I'd like to be able to do is pull out the following:


  booking_id |                    slot_time
------------+-----------------------------------------------------
           1 | ["2021-06-20 12:00:00+01","2021-06-20 14:00:00+01")
           2 | ["2021-06-20 14:00:00+01","2021-06-20 16:00:00+01")


As you can see, where the two slots overlap (between 13:00 and 14:00), 
the booking with the higher priority (lower booking ID) takes precedence.

The closest I've got to it is this:


select a.booking_id, a.booking_time - b.booking_time from bookings a
inner join bookings b on (a.booking_id < b.booking_id)
union
select d.booking_id, d.booking_time - c.booking_time from bookings d
inner join bookings c on (d.booking_id > c.booking_id)


This gives me:


  booking_id |                      ?column?
------------+-----------------------------------------------------
           1 | ["2021-06-20 12:00:00+01","2021-06-20 13:00:00+01")
           2 | ["2021-06-20 14:00:00+01","2021-06-20 16:00:00+01")


...which is missing the bit where they overlap; and anyway, when I add 
in more bookings, it gives me nonsense results. :-)

Any pointers will be greatly appreciated!

Many thanks in advance,

Ray.


-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie



Re: Overlapping timestamptz ranges with priority

From
Adrian Klaver
Date:
On 6/27/21 3:41 PM, 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.
> 
> Here's a slightly simplified example:
> 
> 
> create table bookings (
>      booking_id bigint not null,
>      booking_time tstzrange not null,
> 
>      constraint bookings_pk primary key (booking_id)
> );

It seems to me this is missing some reference to what is being booked 
e.g. room number.

> 

> 
> ...which is missing the bit where they overlap; and anyway, when I add 
> in more bookings, it gives me nonsense results. :-)
> 
> Any pointers will be greatly appreciated!
> 
> Many thanks in advance,
> 
> Ray.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Overlapping timestamptz ranges with priority

From
Alban Hertroys
Date:
> On 28 Jun 2021, at 0:41, Ray O'Donnell <ray@rodonnell.ie> wrote:
>
> Hi all,
>

(…)

> create table bookings (
>    booking_id bigint not null,
>    booking_time tstzrange not null,
>
>    constraint bookings_pk primary key (booking_id)
> );
>
> insert into bookings (booking_id, booking_time) values
> (1, tstzrange('2021-06-20 12:00+01', '2021-06-20 14:00+01', '[)')),
> (2, tstzrange('2021-06-20 13:00+01', '2021-06-20 16:00+01', '[)'));
>
>
> And what I'd like to be able to do is pull out the following:
>
>
> booking_id |                    slot_time
> ------------+-----------------------------------------------------
>          1 | ["2021-06-20 12:00:00+01","2021-06-20 14:00:00+01")
>          2 | ["2021-06-20 14:00:00+01","2021-06-20 16:00:00+01")


You could probably achieve this by using window function lag() over (order by booking_id), in combination with a case
statementwhen the range from the previous row overlaps the current range. 

That would only solve the case for immediately subsequent rows though, if you have multiple rows overlapping you will
needto track the first range in that list. 

Another possible route is a recursive CTE, with a similar approach.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.




Re: Overlapping timestamptz ranges with priority

From
Ray O'Donnell
Date:
On 28/06/2021 00:52, Adrian Klaver wrote:
> On 6/27/21 3:41 PM, Ray O'Donnell wrote:

>> Here's a slightly simplified example:
>>
>>
>> create table bookings (
>>      booking_id bigint not null,
>>      booking_time tstzrange not null,
>>
>>      constraint bookings_pk primary key (booking_id)
>> );
> 
> It seems to me this is missing some reference to what is being booked 
> e.g. room number.

Yes, indeed - I left out everything except what was immediately relevant 
to my problem. The real table is actually for booking aircraft - it's 
for the local flying club of which I'm a member - so there are columns 
for aircraft registration, member details, etc.

Ray.

-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie



Re: Overlapping timestamptz ranges with priority

From
Adrian Klaver
Date:
On 6/28/21 3:05 AM, Ray O'Donnell wrote:
> On 28/06/2021 00:52, Adrian Klaver wrote:
>> On 6/27/21 3:41 PM, Ray O'Donnell wrote:
> 
>>> Here's a slightly simplified example:
>>>
>>>
>>> create table bookings (
>>>      booking_id bigint not null,
>>>      booking_time tstzrange not null,
>>>
>>>      constraint bookings_pk primary key (booking_id)
>>> );
>>
>> It seems to me this is missing some reference to what is being booked 
>> e.g. room number.
> 
> Yes, indeed - I left out everything except what was immediately relevant 
> to my problem. The real table is actually for booking aircraft - it's 
> for the local flying club of which I'm a member - so there are columns 
> for aircraft registration, member details, etc.

An ounce of prevention is worth a pound of cure:

1) Install btree_gist
create extension btree_gist ;

2) create table bookings (
     booking_id bigint not null,
     aircraft_id integer,
     booking_time_start timestamptz,
     booking_time_end timestamptz,

     constraint bookings_pk primary key (booking_id),
     constraint timestamp_exclude EXCLUDE USING gist
         (aircraft_id WITH =,
          tstzrange(booking_time_start, booking_time_end, '[]') WITH &&)
);

3)
insert into bookings (booking_id, aircraft_id,  booking_time_start, 
booking_time_end) values
(1, 1, '2021-06-20 12:00+01', '2021-06-20 14:00+01');
INSERT 0 1

insert into bookings (booking_id, aircraft_id,  booking_time_start, 
booking_time_end) values
(2, 1, '2021-06-20 13:00+01', '2021-06-20 16:00+01');
ERROR:  conflicting key value violates exclusion constraint 
"timestamp_exclude"
DETAIL:  Key (aircraft_id, tstzrange(booking_time_start, 
booking_time_end, '[]'::text))=(1, ["2021-06-20 05:00:00-07","2021-06-20 
08:00:00-07"]) conflicts with existing key (aircraft_id, 
tstzrange(booking_time_start, booking_time_end, '[]'::text))=(1, 
["2021-06-20 04:00:00-07","2021-06-20 06:00:00-07"]).


This way the overlap is prevented and you don't have to deal with it later.

> 
> Ray.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Overlapping timestamptz ranges with priority

From
Ray O'Donnell
Date:
On 29/06/2021 20:43, Adrian Klaver wrote:
> 
> An ounce of prevention is worth a pound of cure:
> 
> 1) Install btree_gist
> create extension btree_gist ;
> 
> 2) create table bookings (
>      booking_id bigint not null,
>      aircraft_id integer,
>      booking_time_start timestamptz,
>      booking_time_end timestamptz,
> 
>      constraint bookings_pk primary key (booking_id),
>      constraint timestamp_exclude EXCLUDE USING gist
>          (aircraft_id WITH =,
>           tstzrange(booking_time_start, booking_time_end, '[]') WITH &&)

[...]

> This way the overlap is prevented and you don't have to deal with it later.

Fair point.... The idea of using overlapping ranges was to allow for 
queued bookings, which is something we permit. In the old system (which 
this one is to replace) queued bookings are kept in a separate table. My 
idea was to have them in a single table, which would seem more elegant - 
but by golly it's harder! Maybe I should rethink my approach.

Thanks,

Ray.


-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie



Re: Overlapping timestamptz ranges with priority

From
Adrian Klaver
Date:
On 6/29/21 12:49 PM, Ray O'Donnell wrote:
> On 29/06/2021 20:43, Adrian Klaver wrote:
>>
>> An ounce of prevention is worth a pound of cure:
>>
>> 1) Install btree_gist
>> create extension btree_gist ;
>>
>> 2) create table bookings (
>>      booking_id bigint not null,
>>      aircraft_id integer,
>>      booking_time_start timestamptz,
>>      booking_time_end timestamptz,
>>
>>      constraint bookings_pk primary key (booking_id),
>>      constraint timestamp_exclude EXCLUDE USING gist
>>          (aircraft_id WITH =,
>>           tstzrange(booking_time_start, booking_time_end, '[]') WITH &&)
> 
> [...]
> 
>> This way the overlap is prevented and you don't have to deal with it 
>> later.
> 
> Fair point.... The idea of using overlapping ranges was to allow for 
> queued bookings, which is something we permit. In the old system (which 
> this one is to replace) queued bookings are kept in a separate table. My 
> idea was to have them in a single table, which would seem more elegant - 
> but by golly it's harder! Maybe I should rethink my approach.

The queued bookings are for a particular aircraft or a particular time slot?

> 
> Thanks,
> 
> Ray.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Overlapping timestamptz ranges with priority

From
Ray O'Donnell
Date:
On 29/06/2021 22:10, Adrian Klaver wrote:
> 
> The queued bookings are for a particular aircraft or a particular time 
> slot?

They're for an aircraft. On the old system, they could only be for a 
slot - so if someone had booked, say, a two-hour slot, then anyone 
queued behind them could only queue for the same two hours. I'd like to 
make the new system more flexible, hence the potentially overlapping ranges.

Ray.

-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie



Re: Overlapping timestamptz ranges with priority

From
Adrian Klaver
Date:
On 6/29/21 2:30 PM, Ray O'Donnell wrote:
> On 29/06/2021 22:10, Adrian Klaver wrote:
>>
>> The queued bookings are for a particular aircraft or a particular time 
>> slot?
> 
> They're for an aircraft. On the old system, they could only be for a 
> slot - so if someone had booked, say, a two-hour slot, then anyone 
> queued behind them could only queue for the same two hours. I'd like to 
> make the new system more flexible, hence the potentially overlapping 
> ranges.

If I'm following correctly then:

1) Under old system there where preset two hour slots over a day period, 
where the period was say 8:00 to 16:00

2) You now want to allow user defined two hour slots over the same 
period, where a slot can't start before 8:00 or end after 16:00.

3) First to file gets the slot.

4) Because of turn around considerations a user can't pick up the 
remaining hour of an overlap. As example in the case of time slots of 
10:00 - 12:00 and 11:00 - 13:00 for a particular plane where the second 
slot was the later one filed the user can't get 12:00 - 13:00.



> 
> Ray.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Overlapping timestamptz ranges with priority

From
Ray O'Donnell
Date:
On 29/06/2021 22:49, Adrian Klaver wrote:
> If I'm following correctly then:
> 
> 1) Under old system there where preset two hour slots over a day period, 
> where the period was say 8:00 to 16:00
> 
> 2) You now want to allow user defined two hour slots over the same 
> period, where a slot can't start before 8:00 or end after 16:00.
> 
> 3) First to file gets the slot.
> 
> 4) Because of turn around considerations a user can't pick up the 
> remaining hour of an overlap. As example in the case of time slots of 
> 10:00 - 12:00 and 11:00 - 13:00 for a particular plane where the second 
> slot was the later one filed the user can't get 12:00 - 13:00.

Sorry for the slow response - I'm only getting to spend intermittent 
time on this project.

Your description isn't far off; however (with reference to your points 
1-4 above):

1. Under the old system, one-hour slots were baked in at a fundamental 
level, though a user could book multiple consecutive 1-hour slots together.

2. I'd now like the database to be agnostic with regard to the slot 
duration. A booking should be defined only by timestamps defining its 
beginning and end; the booking could also span multiple days. The 
concept of a "slot" (a slot being the minimum unit of time which a user 
can book, be it an hour or anything else) will be imposed only at the 
application level... This would allow it to be changed easily if 
required (in other words, slots are a sort of artifial grid view which 
the application imposes on the raw bookings coming from the database).

3. Yes, first to book available time gets it. Anyone else booking for 
the same time is in a queue behind the first in line. Similarly, someone 
making a booking whose time partially overlaps an existing booking will 
effectively finish up with two bookings - one active (first in line for 
the free time) and one queued (behind the already-booked time).

4. Effectively, yes - see my explanation of queued bookings in (3) above.

Thanks for the time and thought you're giving this!

Ray.


-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie



Re: Overlapping timestamptz ranges with priority

From
Ray O'Donnell
Date:
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;
$$;



-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie



Re: Overlapping timestamptz ranges with priority

From
Adrian Klaver
Date:
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



Re: Overlapping timestamptz ranges with priority

From
Ray O'Donnell
Date:
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



Re: Overlapping timestamptz ranges with priority

From
Adrian Klaver
Date:
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



Re: Overlapping timestamptz ranges with priority

From
Ray O'Donnell
Date:
On 03/07/2021 21:13, Adrian Klaver wrote:
> On 7/3/21 12:16 PM, Ray O'Donnell wrote:
>> 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?

Whoops, sorry, here it is:

     for m_overlapping_time in
         select booking_id, booking_time from bookings
         where aircraft_reg = m_rec.aircraft_reg
           and booking_id < m_rec.booking_id
           and booking_time && m_rec.booking_time
     loop
         [... etc ...]

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

Ah, good - thanks for pointing that out.

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

I see what you mean. It shouldn't matter for our use case; ordering on 
the aircraft registration and time is what counts for us, and the output 
of the function ought to produce well-ordered booking times for each 
aircraft. The other columns are used for display purposes only.

>> 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 again for your help - much appreciated!

Ray.


-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie