> On 28 Jun 2021, at 0:41, Ray O'Donnell <[email protected]> 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.
