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