8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Aggregation over INTERVAL Datatypes in Oracle Database 23c
Oracle 23c allows us to use the SUM
and AVG
aggregate and analytic functions with INTERVAL
datatypes for the first time.
Setup
The examples in this article require the following table.
drop table if exists t1 purge; create table t1 ( id number, start_time timestamp, end_time timestamp, duration interval day to second generated always as (end_time - start_time) virtual ); insert into t1 (id, start_time, end_time) values (1, timestamp '2023-04-10 08:45:00.0', timestamp '2023-04-10 18:01:00.0'); insert into t1 (id, start_time, end_time) values (2, timestamp '2023-04-11 09:00:00.0', timestamp '2023-04-11 17:00:00.0'); insert into t1 (id, start_time, end_time) values (3, timestamp '2023-04-12 08:00:00.0', timestamp '2023-04-12 17:45:00.0'); insert into t1 (id, start_time, end_time) values (4, timestamp '2023-04-13 07:00:00.0', timestamp '2023-04-13 16:00:00.0'); commit;
When we display the data we can see the virtual column DURATION
is showing the INTERVAL
between the START_TIME
and END_TIME
values.
alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss'; select * from t1; ID START_TIME END_TIME DURATION ---------- -------------------- -------------------- -------------------- 1 2023-04-10 08:45:00 2023-04-10 18:01:00 +00 09:16:00.000000 2 2023-04-11 09:00:00 2023-04-11 17:00:00 +00 08:00:00.000000 3 2023-04-12 08:00:00 2023-04-12 17:45:00 +00 09:45:00.000000 4 2023-04-13 07:00:00 2023-04-13 16:00:00 +00 09:00:00.000000 SQL>
The Problem
We have been able to use the MIN
and MAX
aggregate and analytic functions on INTERVAL
data type columns for along time.
column min_duration format a20 column max_duration format a20 select min(duration) as min_duration, max(duration) as max_duration from t1; MIN_DURATION MAX_DURATION -------------------- -------------------- +00 08:00:00.000000 +00 09:45:00.000000 SQL> select id, start_time, end_time, duration, min(duration) over () as min_duration, max(duration) over () as max_duration from t1; ID START_TIME END_TIME DURATION MIN_DURATION MAX_DURATION ---------- -------------------- -------------------- -------------------- -------------------- -------------------- 1 2023-04-10 08:45:00 2023-04-10 18:01:00 +00 09:16:00.000000 +00 08:00:00.000000 +00 09:45:00.000000 2 2023-04-11 09:00:00 2023-04-11 17:00:00 +00 08:00:00.000000 +00 08:00:00.000000 +00 09:45:00.000000 3 2023-04-12 08:00:00 2023-04-12 17:45:00 +00 09:45:00.000000 +00 08:00:00.000000 +00 09:45:00.000000 4 2023-04-13 07:00:00 2023-04-13 16:00:00 +00 09:00:00.000000 +00 08:00:00.000000 +00 09:45:00.000000 SQL>
If we try to use the SUM
or AVG
aggregate functions on an INTERVAL
data type, we get an error on previous versions of the database.
select sum(duration) from t1; * ERROR at line 1: ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND SQL> select avg(duration) from t1 * ERROR at line 1: ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND SQL>
The Solution
Oracle 23c allows us to use the SUM
and AVG
aggregate functions with INTERVAL
datatypes.
select sum(duration) from t1; SUM(DURATION) --------------------------------------------------------------------------- +000000001 12:01:00.000000000 SQL> select avg(duration) from t1; AVG(DURATION) --------------------------------------------------------------------------- +000000000 09:00:15.000000000 SQL>
We can also use SUM
and AVG
as analytic functions, allowing us to display the raw data along with the aggregated value.
set linesize 120 column sum_duration format a30 select id, start_time, end_time, duration, sum(duration) over () as sum_duration from t1; ID START_TIME END_TIME DURATION SUM_DURATION ---------- -------------------- -------------------- -------------------- ------------------------------ 1 2023-04-10 08:45:00 2023-04-10 18:01:00 +00 09:16:00.000000 +000000001 12:01:00.000000000 2 2023-04-11 09:00:00 2023-04-11 17:00:00 +00 08:00:00.000000 +000000001 12:01:00.000000000 3 2023-04-12 08:00:00 2023-04-12 17:45:00 +00 09:45:00.000000 +000000001 12:01:00.000000000 4 2023-04-13 07:00:00 2023-04-13 16:00:00 +00 09:00:00.000000 +000000001 12:01:00.000000000 SQL> column avg_duration format a30 select id, start_time, end_time, duration, avg(duration) over () as avg_duration from t1; ID START_TIME END_TIME DURATION AVG_DURATION ---------- -------------------- -------------------- -------------------- ------------------------------ 1 2023-04-10 08:45:00 2023-04-10 18:01:00 +00 09:16:00.000000 +000000000 09:00:15.000000000 2 2023-04-11 09:00:00 2023-04-11 17:00:00 +00 08:00:00.000000 +000000000 09:00:15.000000000 3 2023-04-12 08:00:00 2023-04-12 17:45:00 +00 09:45:00.000000 +000000000 09:00:15.000000000 4 2023-04-13 07:00:00 2023-04-13 16:00:00 +00 09:00:00.000000 +000000000 09:00:15.000000000 SQL>
For more information see:
Hope this helps. Regards Tim...