Re: Overlapping timestamptz ranges with priority

On 29/06/2021 22:49, Adrian Klaver wrote:
> If I'm following correctly then:
>
> 1) Under old system there where preset two hour slots over a day period,
> where the period was say 8:00 to 16:00
>
> 2) You now want to allow user defined two hour slots over the same
> period, where a slot can't start before 8:00 or end after 16:00.
>
> 3) First to file gets the slot.
>
> 4) Because of turn around considerations a user can't pick up the
> remaining hour of an overlap. As example in the case of time slots of
> 10:00 - 12:00 and 11:00 - 13:00 for a particular plane where the second
> slot was the later one filed the user can't get 12:00 - 13:00.

Sorry for the slow response - I'm only getting to spend intermittent
time on this project.

Your description isn't far off; however (with reference to your points
1-4 above):

1. Under the old system, one-hour slots were baked in at a fundamental
level, though a user could book multiple consecutive 1-hour slots together.

2. I'd now like the database to be agnostic with regard to the slot
duration. A booking should be defined only by timestamps defining its
beginning and end; the booking could also span multiple days. The
concept of a "slot" (a slot being the minimum unit of time which a user
can book, be it an hour or anything else) will be imposed only at the
application level... This would allow it to be changed easily if
required (in other words, slots are a sort of artifial grid view which
the application imposes on the raw bookings coming from the database).

3. Yes, first to book available time gets it. Anyone else booking for
the same time is in a queue behind the first in line. Similarly, someone
making a booking whose time partially overlaps an existing booking will
effectively finish up with two bookings - one active (first in line for
the free time) and one queued (behind the already-booked time).

4. Effectively, yes - see my explanation of queued bookings in (3) above.

Thanks for the time and thought you're giving this!

Ray.

--
Raymond O'Donnell // Galway // Ireland
ray(at)rodonnell(dot)ie