Overlapping timestamptz ranges with priority

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

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 statement when 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 need to 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.

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

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

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

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

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

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

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

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

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

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

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

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