On 11/08/2026 23:59, Brent Wood wrote:
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.
Hi Brent,
Thanks very much for responding - it sounds as if TimescaleDB would be
overkill for what we need, but good to know that it's out there.
Best regards,
Ray.
Cheers,
Brent Wood
------------------------------------------------------------------------
*From:* Ray O'Donnell <[email protected]>
*Sent:* Wednesday, 12 August 2026 9:32 am
*To:* pgsql-general <[email protected]>
*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 [email protected]
<mailto:[email protected]>
*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>.
--
Ray O'Donnell // Galway // Ireland
[email protected]
