[Wranglers] TIME_BUCKET function in Oracle

[Ali, Saqib]

A nice blogpost by Laurent Schneider on TIME_BUCKET function in Oracle:
https://laurentschneider.com/wordpress/2025/03/time_bucket-group-by-time-period.html

On Tue, Jul 8, 2025 at 7:27 AM Ali, Saqib <docbook.xml at gmail.com> wrote:

> 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/20250715/b875e9b2/attachment.htm>