Oracle 23ai: INTERVAL Data Type Aggregations – Eclipsys

Overview:

• Oracle 23ai introduces the use of SUM and AVG functions with INTERVAL datatype
• This enhancement makes it easier to calculate totals and averages over INTERVAL values

In this blog, I’ll demonstrate the use of SUM and AVG functions with INTERVAL datatype

Prerequisites:

• Oracle Database 23ai Free Developer Release

Step #1: Preparation

The example in this blog requires the following table

drop table if exists trips purge;

create table trips (
  id          number,
  start_time  timestamp,
  end_time    timestamp,
  duration    interval day to second generated always as (end_time - start_time) virtual
);

begin
insert into trips (id, start_time, end_time) 
values (1, timestamp '2024-01-20 08:45:00.0', timestamp '2024-01-20 18:01:00.0');
insert into trips (id, start_time, end_time) 
values (2, timestamp '2024-01-22 09:00:00.0', timestamp '2024-01-22 17:00:00.0');
insert into trips (id, start_time, end_time) 
values (3, timestamp '2024-01-25 08:00:00.0', timestamp '2024-01-25 17:45:00.0');
insert into trips (id, start_time, end_time) 
values (4, timestamp '2024-01-27 07:00:00.0', timestamp '2024-01-27 16:00:00.0');
insert into trips (id, start_time, end_time) 
values (5, timestamp '2024-01-28 07:00:00.0', timestamp '2024-01-28 16:00:00.0');
insert into trips (id, start_time, end_time) 
values (6, timestamp '2024-01-29 07:00:00.0', timestamp '2024-01-29 16:00:00.0');
insert into trips (id, start_time, end_time) 
values (7, timestamp '2024-01-30 07:00:00.0', timestamp '2024-01-30 16:00:00.0');
insert into trips (id, start_time, end_time) 
values (8, timestamp '2024-01-31 07:00:00.0', timestamp '2024-01-31 16:00:00.0');
commit;
end;
/

Step #2: Testing 

1. If we use SUM or AVG functions on an INTERVAL datatype on the pre-23ai Oracle database, we will get an error as shown below.

2. Oracle 23ai database allows the use of SUM and AVG functions with INTERVAL datatype

select sum(duration),avg(duration) from trips;

– We can also use SUM and AVG as analytics functions with INTERVAL datatype

select id,start_time,end_time,duration,
sum(duration) over (order by id rows unbounded preceding) as DUR_RUN_TOTAL,
avg(duration) over (order by id rows unbounded preceding) as DUR_RUN_AVG
from trips;