Oracle 23ai: INTERVAL data type aggregations

 

- 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 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;