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 Problem
The Solution

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:

Datetime and Interval Examples

Hope this helps. Regards Tim...

Back to the Top.