Re: Overlapping timestamptz ranges with priority

On 6/27/21 3:41 PM, 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.
>
> 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)
> );

It seems to me this is missing some reference to what is being booked
e.g. room number.

>

>
> ...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.
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com