Hi all,
I need some help constructing a query... Short version is that I need to
group time-slots together into larger ones.
Say I have the following rows, each representing a one-hour slot in a
booking system (these are manufactured by a function, pulling data from
underlying tables, and this is a simplified example):
aircraft_reg | slot_begin | slot_end |
booking_id | booking_priority | owner_uid
--------------+------------------------+------------------------+------------+------------------+-----------
EI-MCG | 2026-08-22 09:00:00+01 | 2026-08-22 10:00:00+01 |
361 | 1 | rod
EI-MCG | 2026-08-22 10:00:00+01 | 2026-08-22 11:00:00+01 |
361 | 1 | rod
EI-MCG | 2026-08-22 11:00:00+01 | 2026-08-22 12:00:00+01 |
361 | 1 | rod
EI-MCG | 2026-08-22 12:00:00+01 | 2026-08-22 13:00:00+01 |
217 | 1 | jbloggs
EI-MCG | 2026-08-22 12:00:00+01 | 2026-08-22 13:00:00+01 |
361 | 2 | rod
EI-MCG | 2026-08-22 13:00:00+01 | 2026-08-22 14:00:00+01 |
217 | 1 | jbloggs
EI-MCG | 2026-08-22 13:00:00+01 | 2026-08-22 14:00:00+01 |
361 | 2 | rod
EI-MCG | 2026-08-22 14:00:00+01 | 2026-08-22 15:00:00+01 |
361 | 1 | rod
EI-MCG | 2026-08-22 15:00:00+01 | 2026-08-22 16:00:00+01 |
361 | 1 | rod
EI-MCG | 2026-08-22 16:00:00+01 | 2026-08-22 17:00:00+01 |
361 | 1 | rod
EI-MCG | 2026-08-22 17:00:00+01 | 2026-08-22 18:00:00+01 |
361 | 1 | rod
EI-MCG | 2026-08-22 18:00:00+01 | 2026-08-22 19:00:00+01 |
361 | 1 | rod
(In the case of the slots at 12:00 and 13:00, the slot owner is user
"jbloggs", and user "rod" is queuing in the hope that jbloggs cancels -
the booking_priority column indicates who has the active booking and who
is queued.)
My question is: how do I group together adjacent slots into larger
time-slices, so that (for example) I can tell user "rod" that his
booking with ID 361 looks like this? -
* 09:00 - 12:00: active booking
* 12:00 - 14:00: queued booking
* 14:00 - 19:00: active booking
...i.e. reduce all the row above into just three rows.
For context, the bookings are stored in an underlying table which uses a
tstzrange column for the booking time. When bookings overlap the
overlapping period is queued behind booking(s) made earlier - hence the
booking for user "rod" in the example above has the same booking ID for
all its hour slots.
Here's an example of what I've tried. The function get_slots_demo() in
the CTE breaks the overall time-period covered into hour-long slots, as
returned in the first example above.
with slots as (
select * from get_slots_demo(
(select lower(booking_time) from bookings_demo where
booking_id = 361),
(select upper(booking_time) from bookings_demo where
booking_id = 361)
)
where booking_id = 361
order by slot_begin, booking_priority
)
select
s1.booking_id,
s1.aircraft_reg,
min(s1.slot_begin) as booking_begin,
max(s2.slot_end) as booking_end,
s1.booking_priority
from slots s1
inner join slots s2 on (s1.slot_end = s2.slot_begin)
group by s1.booking_id, s1.aircraft_reg, s1.booking_priority;
However, this just returns two rows - one for the entire period and one
for the queued period. This is presumably to be expected, as I suppose
what I really need is some grouping column which will be different for
each of the three periods I want to return... However, I don't have one,
and I can't think of a way to manufacture one. I could do it
procedurally, writing a function which detects the boundary between
active and queued slots and creates the required grouping column that
way, but I'd like to try and do it in "proper SQL" if possible - for the
learning exercise at least!
Any pointers or guidance will be very much appreciated.... Thanks in
advance.
Ray.
--
Ray O'Donnell // Galway // Ireland
[email protected]
