Overlapping timestamptz ranges with priority

["Ray O'Donnell" <ray@xxxxxxxxxxxx>]

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@xxxxxxxxxxxx