Re: Overlapping timestamptz ranges with priority

> On 28 Jun 2021, at 0:41, Ray O'Donnell <ray(at)rodonnell(dot)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.