Oracle 23c - SQL

Aggregation over INTERVAL Data Types in Oracle 23c

/ Oracle DB 23c New Features, SQL 23c / By

In Oracle database 23c, you can pass INTERVAL data types to the SUM and AVG aggregate and analytic functions. This feature streamlines operations involving time intervals, making it more convenient for developers and data analysts. In addition to that, this feature avoids potentially performance-intensive conversions, leading to faster query execution.

In Oracle Database, INTERVAL data types represent a period of time, such as days, hours, minutes, and seconds. Consider the following table which contains Customer Service ticket response times:

CREATE TABLE customer_service_tickets (
    ticket_id NUMBER,
    response_time INTERVAL DAY TO SECOND
);

-- Insert 10 sample rows into customer_service_tickets table with response_time between 1 minute and 1 hour
INSERT INTO customer_service_tickets VALUES (1, INTERVAL '15' MINUTE);
INSERT INTO customer_service_tickets VALUES (2, INTERVAL '45' MINUTE);
INSERT INTO customer_service_tickets VALUES (3, INTERVAL '1' HOUR);
INSERT INTO customer_service_tickets VALUES (4, INTERVAL '30' MINUTE);
INSERT INTO customer_service_tickets VALUES (5, INTERVAL '20' MINUTE);
INSERT INTO customer_service_tickets VALUES (6, INTERVAL '10' MINUTE);
INSERT INTO customer_service_tickets VALUES (7, INTERVAL '40' MINUTE);
INSERT INTO customer_service_tickets VALUES (8, INTERVAL '5' MINUTE);
INSERT INTO customer_service_tickets VALUES (9, INTERVAL '55' MINUTE);
INSERT INTO customer_service_tickets VALUES (10, INTERVAL '25' MINUTE);
commit;

Now, in an pre-23c Oracle database, to obtain the average response time of the tickets, we would write a query like the following:

SELECT 
  NUMTODSINTERVAL(AVG(EXTRACT(DAY FROM response_time)*24*60*60 
                + EXTRACT(HOUR FROM response_time)*60*60 
                + EXTRACT(MINUTE FROM response_time)*60 
                + EXTRACT(SECOND FROM response_time)), 'SECOND') AS average_response_time
FROM customer_service_tickets;

With the introduction of Oracle Database 23c, these INTERVAL types can now be used seamlessly with the SUM and AVG functions. We would simply write a query like the following:

SELECT AVG(response_time) AS avg_response_time FROM customer_service_tickets;

Obviously, this way easier than the pre-23c code.

We can apply the following analytic functions on Interval data types: AVG, SUM, MAX, MIN, LEAD/LAG, and RANK. INTERVAL values cannot be used directly in COUNT, FIRST/LAST, STDDEV, VARIANCE, PERCENT_RANK, CUME_DIST, and NTILE.

Stay tuned for more insights into Oracle Database 23c and other cutting-edge features that empower you to make the most of your database operations. Happy querying!

Scroll to Top