Grouping events by day or month is easy with TRUNC, but six-hour blocks, fifteen-minute slots, or fortnights are not TRUNC units. TIME_BUCKET, new in Oracle AI Database 26ai, returns the start (or end) of the fixed-size bucket a datetime falls into, counting buckets from an origin you choose. Group by its result and you have counts per period of any length.
Code for This Guide
The main example is in the examples/date-functions folder of the Oracle Database 26ai code repository on GitHub, with its output. It queries NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
It comes from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
time_bucket(datetime, stride, origin [, {start | end}])| Argument | Meaning |
|---|---|
| datetime | The value to place in a bucket |
| stride | The bucket size: an interval such as interval '6' hour, or an ISO 8601 duration such as 'P1M' or 'PT15M' |
| origin | The point from which buckets are counted |
| START or END | Return the bucket's start (default) or end |
Count Events per Bucket
The first query counts the departures of 1 January 2026 in six-hour blocks. The second places a date in a monthly bucket and returns both its start and its end.
Example:
select time_bucket(scheduled_departure, interval '6' hour,
timestamp '2026-01-01 00:00:00 UTC') as block_start,
count(*) as departures
from flights
where scheduled_departure >= timestamp '2026-01-01 00:00:00 UTC'
and scheduled_departure < timestamp '2026-01-02 00:00:00 UTC'
group by all
order by 1;
select time_bucket(date '2026-03-15', 'P1M', date '2026-01-01') as month_bucket,
time_bucket(date '2026-03-15', 'P1M', date '2026-01-01', end) as bucket_end
from dual;Output:
BLOCK_START DEPARTURES ___________________________ _____________ 01-JAN-2026 00:00:00 UTC 10 01-JAN-2026 06:00:00 UTC 7 01-JAN-2026 12:00:00 UTC 6 01-JAN-2026 18:00:00 UTC 12 MONTH_BUCKET BUCKET_END _______________ ______________ 01-MAR-2026 01-APR-2026
GROUP BY ALL groups by the bucket without repeating the expression. With END, the bucket of 15 March runs to 1 April, the start of the next bucket.
Fifteen-Minute Slots and the Origin
Smaller strides work the same way. The first query counts morning departures from Dubai in fifteen-minute slots; the second shows how the origin decides where buckets begin.
Example:
-- departures from Dubai on the morning of 15 March, in 15-minute buckets
select time_bucket(f.scheduled_departure, interval '15' minute,
timestamp '2026-03-15 00:00:00 Asia/Dubai') as slot,
count(*) as departures
from flights f join routes r on r.route_id = f.route_id
where r.origin = 'DXB'
and f.scheduled_departure >= timestamp '2026-03-15 06:00:00 Asia/Dubai'
and f.scheduled_departure < timestamp '2026-03-15 12:00:00 Asia/Dubai'
group by all
order by 1;
-- the origin decides where buckets start
select time_bucket(timestamp '2026-03-15 10:20:00', interval '1' hour,
timestamp '2026-03-15 00:00:00') as from_midnight,
time_bucket(timestamp '2026-03-15 10:20:00', interval '1' hour,
timestamp '2026-03-15 00:30:00') as from_half_past
from dual;Output:
SLOT DEPARTURES __________________________________ _____________ 15-MAR-2026 07:15:00 ASIA/DUBAI 2 15-MAR-2026 08:15:00 ASIA/DUBAI 1 15-MAR-2026 09:15:00 ASIA/DUBAI 1 FROM_MIDNIGHT FROM_HALF_PAST _______________________ _______________________ 15-MAR-2026 10:00:00 15-MAR-2026 09:30:00
Only slots with departures appear; join to a generated series of slots if empty ones must show too. With hourly buckets counted from midnight, 10:20 falls in the bucket starting at 10:00; counted from 00:30, it falls in the one starting at 09:30.
Things to Know
- Give the origin the same data type as the datetime, with a time zone for TIMESTAMP WITH TIME ZONE values, so buckets start where you expect.
- Month and year strides follow the calendar, so monthly buckets have different lengths.
- TRUNC is still simpler for the standard units: trunc(d, 'HH') for hours, trunc(d, 'MM') for months.
Related Guides
Conclusion
TIME_BUCKET places a datetime in a fixed-size bucket counted from an origin and returns the bucket's start or end. Use it with GROUP BY to count events per period of any length, choose the origin to align the buckets, and fill empty buckets with a join to a generated series when they must appear.
