[Wranglers] TIME_BUCKET function in Oracle

[Ali, Saqib]

Another blogspot on time_bucket in Oracle 23.7:
https://www.geraldonit.com/whats-new-for-developers-in-oracle-database-23-7/



On Tue, Jul 15, 2025 at 11:15 AM Ali, Saqib <docbook.xml at gmail.com> wrote:

> 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/20250718/6bdae9c0/attachment.htm>