[Wranglers] TIME_BUCKET function in Oracle

[Ali, Saqib]

TIME_BUCKET function in Oracle simplifies the logic to place rows into
N-unit time slices – e.g., five minutes, two hours, or three days. It takes
4 parameters:

The datetime – input value to bucket
The stride – the size of each group as an interval e.g. '10' minute, '1'
day, '12' hour etc.
The origin – the anchor date for calculating start and end times. Must of
the same datatype as the datetime input (parameter 1)
The start_or_end – whether to return the START or END of each bucket
(optional; defaults to START)

Here is an example on how to use the TIME_BUCKET function to bucket orders
into 15 minute intervals:

with orders as (
select 1 as order_number, TIMESTAMP '2025-06-30 09:36:50' as order_date
union
select 2, TIMESTAMP '2025-06-30 09:45:01'
union
select 3, TIMESTAMP '2025-06-30 10:45:01'
union
select 4, TIMESTAMP '2025-06-30 10:46:01'

)
select
TIME_BUCKET (order_date, INTERVAL '10' minute, CAST(trunc(order_date) AS
TIMESTAMP(0) ), START) bucket_start
, TIME_BUCKET (order_date, INTERVAL '10' minute, CAST(trunc(order_date) AS
TIMESTAMP(0) ) , END) bucket_end
, count(distinct order_number) as number_of_orders
from orders
group by bucket_start, bucket_end;

-- output:
"BUCKET_START"                "BUCKET_END"
 "NUMBER_OF_ORDERS"
"30/06/25 09:30:00" "30/06/25 09:40:00" "1"
"30/06/25 09:40:00" "30/06/25 09:50:00" "1"
"30/06/25 10:40:00" "30/06/25 10:50:00" "2"
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://analyticsengineering.net/pipermail/wranglers/attachments/20250708/6d3b83d2/attachment.htm>