Re: SQL help:

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 <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>.

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