Re: SQL help:

Hi Ray,

I'm not sure this is what you want, but we are using TimescaleDB (a Postgres extension, now Tiger Data) with time series data.

We are storing billions of sensor readings in Timescale/Postgres and have found it very effective & performant.

Timescale provides several extra SQL functions for querying time based data, including the concept of time buckets, that may well support exactly what you are trying to do.
Note that while a Timescale database acts pretty much like a normal Postgres one, there are significant things happening behind the scenes, like automatic table partitioning
based on timestamps that you may want to be aware of.

I know core Postgres is also adding more time series support in later versions, so checking just what is there in the latest release & seeing if it helps might be useful.

Cheers,

Brent Wood

________________________________
From: Ray O'Donnell <ray(at)rodonnell(dot)ie>
Sent: Wednesday, 12 August 2026 9:32 am
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: SQL help:

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
ray(at)rodonnell(dot)ie<mailto:ray(at)rodonnell(dot)ie>

Brent Wood
Principal Technician - GIS and Spatial Data Management
+64-4-386-0529
301 Evans Bay Parade, Greta Point, Hataitai, Wellington, New Zealand
Earth Sciences New Zealand
[Earth Sciences New Zealand]<https://earthsciences.nz>
The Institute of Geological and Nuclear Sciences Limited and the National Institute of Water and Atmospheric Research Limited joined to become the New Zealand Institute for Earth Science Limited. We are known as Earth Sciences New Zealand. For more information on the Earth Sciences transition click here<https://niwa.co.nz/about-niwa/science-sector-reforms>.

Notice: This email and any attachments may contain information which is confidential and/or subject to copyright or legal privilege, and may not be used, published or redistributed without the prior written consent of Earth Sciences New Zealand. If you are not the intended recipient, please immediately notify the sender and delete the email and any attachments. Any opinion or views expressed in this email are those of the individual sender and may not represent those of Earth Sciences New Zealand.

For information about how we process data and monitor communications please see our privacy policy<https://earthsciences.nz/privacy-policy>.