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
