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.klaver@aklaver.com