Обсуждение: Overlapping timestamptz ranges with priority
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
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
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.
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