Dates: TIME_BUCKET, CEIL and FLOOR, and adding up intervals

Three date jobs that took arithmetic on 19c and take one function on 26ai: putting dates into fixed-size buckets, rounding a date up, and adding up intervals.

Part 1: weekly buckets that start on match day

The test case

The 2025 season started on Saturday, April 5. Count the matches in each week of the season, where week 1 runs from April 5 to April 11, week 2 from April 12, and so on.

TRUNC(date, 'IW') gives ISO weeks, which start on Monday. That's not what we want here: our weeks start on the season's first day.

Way 1: TIME_BUCKET

26ai only

select to_char(time_bucket(match_date, interval '7' day, date '2025-04-05'), 'YYYY-MM-DD') as week_start,
       count(*)      as matches,
       min(match_no) as first_match,
       max(match_no) as last_match
from   cr_matches
where  season = 2025
group  by time_bucket(match_date, interval '7' day, date '2025-04-05')
order  by week_start;

TIME_BUCKET(value, width, origin) drops each date into a bucket width wide, counting from origin, and returns the bucket's start. Add a fourth argument END to get the bucket's end instead. It works for timestamps with any width: interval '15' minute buckets are how you'd chart ball-by-ball data against a clock.

Way 2: the arithmetic (19c)

select to_char(date '2025-04-05' + 7 * floor((match_date - date '2025-04-05') / 7), 'YYYY-MM-DD') as week_start,
       count(*)      as matches,
       min(match_no) as first_match,
       max(match_no) as last_match
from   cr_matches
where  season = 2025
group  by date '2025-04-05' + 7 * floor((match_date - date '2025-04-05') / 7)
order  by week_start;

Subtracting two dates gives days; divide by 7, round down, multiply back, add to the origin. Correct, but the intent is buried. TIME_BUCKET says what you mean.

Part 2: rounding a date up

The test case

Each season's prize money is paid on the Monday after the final (or the same day, if the final were on a Monday). What dates are those?

Way 1: CEIL on a date

26ai only

select season,
       to_char(match_date, 'YYYY-MM-DD')             as final_date,
       to_char(ceil(match_date, 'IW'), 'YYYY-MM-DD') as pay_date
from   cr_matches
where  stage = 'FINAL'
order  by season;

TRUNC(date, 'IW') has always rounded down to the Monday that starts the ISO week. 26ai adds CEIL (up to the next Monday, unless it already is one), FLOOR (the same as TRUNC) and ROUND for dates. They take the same format units: 'IW', 'MM', 'YYYY', 'HH' and so on.

Way 2: TRUNC, then add a week if it moved (19c)

select season,
       to_char(match_date, 'YYYY-MM-DD') as final_date,
       to_char(case when trunc(match_date, 'IW') = match_date then match_date
                    else trunc(match_date, 'IW') + 7 end, 'YYYY-MM-DD') as pay_date
from   cr_matches
where  stage = 'FINAL'
order  by season;

Way 3: NEXT_DAY (19c)

select season,
       to_char(match_date, 'YYYY-MM-DD')                 as final_date,
       to_char(next_day(match_date - 1, 'MONDAY'), 'YYYY-MM-DD') as pay_date
from   cr_matches
where  stage = 'FINAL'
order  by season;

NEXT_DAY(d, 'MONDAY') is the first Monday after d, so starting from the day before includes d itself. One catch: the day name is read in the session's date language, so 'MONDAY' fails in a German session. TRUNC(..., 'IW') and CEIL(..., 'IW') don't depend on language. (Prize money on a Monday, computed to the day: in our league even the payroll is deterministic.)

Part 3: adding up intervals

The test case

Add up the gaps between consecutive matches in each season. (It should come to the number of days from the first match to the last: a good check.)

Way 1: SUM of an INTERVAL

26ai only

select season, extract(day from sum(gap)) as days
from   (select season,
               numtodsinterval(lead(match_date) over (partition by season order by match_date) - match_date, 'DAY') as gap
        from   cr_matches)
group  by season
order  by season;

On 26ai SUM and AVG work on INTERVAL values, and give an interval back. That matters for real durations: match lengths, time between deliveries, time to resolve a ticket.

The last match of each season has no next match, so its gap is null, and SUM skips nulls.

Way 2: sum numbers of days (19c)

select season, sum(gap_days) as days
from   (select season,
               lead(match_date) over (partition by season order by match_date) - match_date as gap_days
        from   cr_matches)
group  by season
order  by season;

On 19c, SUM of an interval is ORA-00932: inconsistent datatypes, so you keep durations as numbers (days, or seconds) and convert at the end with NUMTODSINTERVAL.

19c and 26ai

Gotchas

• ORDER BY week_start sorts text here, because of the TO_CHAR. YYYY-MM-DD sorts correctly as text; DD/MM/YYYY wouldn't. Sort by the date itself when the format isn't year-first.
• EXTRACT(DAY FROM interval) is only the days part, not the total: an interval of 1 day 12 hours gives 1.
• Dates carry a time. DATE '2025-04-05' is midnight. Data with times needs TRUNC before comparing to a date literal, on both versions.