From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Ray O'Donnell <ray(at)rodonnell(dot)ie>, 'PostgreSQL' <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Overlapping timestamptz ranges with priority |
Date: | 2021-07-03 17:59:02 |
Message-ID: | 816a1158-01aa-ef6b-fe83-a38d7df24d54@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 7/3/21 9:32 AM, Ray O'Donnell wrote:
> On 27/06/2021 23:41, Ray O'Donnell wrote:
>> Hi all,
>>
>> I'm playing with timestamptz ranges for a hobby project. I have a
>> table with a tstzrange column, in which the timestamps can overlap;
>> where they do, rows with a higher priority (derived from a bigint
>> primary key column) should be picked.
>>
>> What I'd like to do is present a view which shows timestamp ranges at
>> the front of the queue, as it were; where ranges overlap, these may
>> be segments of a range from a particular row. I'm having trouble with
>> this and would appreciate suggestions.
>
> I've come up with a way of doing it using a function... it's not going
> to be very efficient if the number of rows gets large, due to nested
> loops, but as the system generally keeps only a limited number of
> bookings (no more that a few hundred), I think it'll do - certainly as a
> first run at it.
>
> Firstly, the table structure (as it now stands) on which the function
> will operate:
>
> CREATE TABLE bookings
> (
> booking_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
> aircraft_reg text NOT NULL,
> type_code text NOT NULL,
> booking_time tstzrange NOT NULL,
> owner_uid text NOT NULL,
> owner_name text NOT NULL,
>
> CONSTRAINT bookings_pk PRIMARY KEY (booking_id),
>
> (... foregin keys etc...)
> );
>
>
> And here's the function:
>
> create or replace function get_visible_bookings()
> returns setof bookings
> language plpgsql
> as
> $$
> declare
> m_rec bookings;
> m_overlapping record;
> m_visible_time tstzrange;
> begin
> -- Loop through all bookings on the system, ordered on booking ID.
> -- The booking ID also give the queue priority of the booking:
> -- bookings with a lower ID have a higher priority.
> for m_rec in
> select * from bookings order by booking_id
> loop
> m_visible_time := m_rec.booking_time;
>
> -- For each booking, check whether there are any with
> -- a higher priority and whose times overlap it.
> for m_overlapping in
> select booking_id, booking_time from bookings
> where booking_id < m_rec.booking_id
> and booking_time && m_rec.booking_time
> loop
> -- Snip away any overlapping (obscured) time.
> m_visible_time := m_visible_time - m_overlapping.booking_time;
> end loop;
>
> -- If any of the current booking's time is still visible,
> -- then return the row with what's left of the time.
> if not isempty(m_visible_time) then
> return next row(m_rec.booking_id, m_rec.aircraft_reg,
> m_rec.type_code, m_visible_time,
> m_rec.owner_uid, m_rec.owner_name);
> end if;
> end loop;
>
> return;
> end;
> $$;
I'm not sure this is doing what you think it is;
select * from bookings order by booking_id;
booking_id | aircraft_reg | type_code |
booking_time | owner_uid | owner_name
------------+--------------+-----------+-----------------------------------------------------+-----------+-----
1 | A1ZX | type1 | ["2021-07-03
10:00:00-07","2021-07-03 14:00:00-07"] | 1 | aklaver
2 | A1ZX | type1 | ["2021-07-03
12:00:00-07","2021-07-03 16:00:00-07"] | 1 | aklaver
3 | A1ZX | type1 | ["2021-07-04
09:00:00-07","2021-07-04 12:00:00-07"] | 1 | aklaver
4 | B2CA | type2 | ["2021-07-03
09:00:00-07","2021-07-03 12:00:00-07"] | 2 | wilbur
5 | B2CA | type2 | ["2021-07-03
11:00:00-07","2021-07-03 14:00:00-07"] | 2 | wilbur
select * from get_visible_bookings();
booking_id | aircraft_reg | type_code |
booking_time | owner_uid | owner_name
------------+--------------+-----------+-----------------------------------------------------+-----------+------------
1 | A1ZX | type1 | ["2021-07-03
10:00:00-07","2021-07-03 14:00:00-07"] | 1 | aklaver
2 | A1ZX | type1 | ("2021-07-03
14:00:00-07","2021-07-03 16:00:00-07"] | 1 | aklaver
3 | A1ZX | type1 | ["2021-07-04
09:00:00-07","2021-07-04 12:00:00-07"] | 1 | aklaver
4 | B2CA | type2 | ["2021-07-03
09:00:00-07","2021-07-03 10:00:00-07") | 2 | wilbur
(4 rows)
The booking_id for aircraft B2CA with booking_time of ["2021-07-03
11:00:00-07","2021-07-03 14:00:00-07"] is not accounted for. There is a
step missing that accounts for bookings being assigned to a particular
aircraft.
>
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Ray O'Donnell | 2021-07-03 19:16:50 | Re: Overlapping timestamptz ranges with priority |
Previous Message | Ben Chobot | 2021-07-03 17:49:57 | Re: Doubt on pgbouncer |