DBA Hub

📋Steps in this guide1/3

Aggregation over INTERVAL Datatypes in Oracle Database 23ai/26ai

Oracle 23ai/26ai allows us to use the SUM and AVG aggregate and analytic functions with INTERVAL datatypes for the first time.

oracle 23configurationintermediate
by OracleDba
14 views
1

Setup

The examples in this article require the following table. When we display the data we can see the virtual column is showing the between the and values.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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'),
  (2, timestamp '2023-04-11 09:00:00.0', timestamp '2023-04-11 17:00:00.0'),
  (3, timestamp '2023-04-12 08:00:00.0', timestamp '2023-04-12 17:45:00.0'),
  (4, timestamp '2023-04-13 07:00:00.0', timestamp '2023-04-13 16:00:00.0');
commit;

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

The Problem

We have been able to use the and aggregate and analytic functions on data type columns for along time. If we try to use the or aggregate functions on an data type, we get an error on previous versions of the database.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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>

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

The Solution

Oracle 23ai/26ai allows us to use the and aggregate functions with datatypes. We can also use and as analytic functions, allowing us to display the raw data along with the aggregated value. For more information see: - Datetime and Interval Examples Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
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>

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!