Calculating sums of interval expressions in Oracle Database 23c

One of the little things that were not possible until the release of Oracle Database 23c is the ability to calculate the sum of multiple interval expressions. What the heck does that mean? Most developers work with interval expressions as soon as they perform arithmetic using timestamps, but that’s still quite an abstract definition.

Please allow me to demonstrate using an example.

Timekeeping

Let’s assume you need to record your daily work time. You start your work day at a certain point in time, work, take a break (or 2), and later on want to know how much time you spent during the breaks, and how much time you worked.

This isn’t too hard to do with the following data model.

create table timekeeping (
    id          number not null,
    begin_work  timestamp not null,
    finish_work timestamp,
    constraint  pk_timekeeping primary key (id)
);

create table breaks (
    id           number not null,
    constraint   pk_breaks primary key (id),
    workday_id   number not null,
    constraint   fk_timekeeping_breaks foreign key (workday_id)
                 references timekeeping,
    start_time   timestamp not null,
    end_time     timestamp
);

Because it’s unknown how many breaks I’m going to have during my day, I like the flexibility to record as many as I like. The timekeeping table records when I start and finish work, the breaks table records the start and end time of my breaks. Most of the bells and whistles (like checking if start time < finish time etc) are missing to keep the example short.

Let’s populate the tables with some totally fictional data:

insert into timekeeping values (
    1,
    to_timestamp('14.01.2024 08:00:00', 'dd.mm.yyyy hh24:mi:ss'),
    to_timestamp('14.01.2024 18:00:00', 'dd.mm.yyyy hh24:mi:ss')
);

insert into timekeeping values (
    2,
    to_timestamp('15.01.2024 09:00:00', 'dd.mm.yyyy hh24:mi:ss'),
    to_timestamp('15.01.2024 18:00:00', 'dd.mm.yyyy hh24:mi:ss')
);

insert into breaks values (
    1,
    1,
    to_timestamp('14.01.2024 09:00:00', 'dd.mm.yyyy hh24:mi:ss'),
    to_timestamp('14.01.2024 09:15:00', 'dd.mm.yyyy hh24:mi:ss')
);

insert into breaks values (
    2,
    1,
    to_timestamp('14.01.2024 13:00:00', 'dd.mm.yyyy hh24:mi:ss'),
    to_timestamp('14.01.2024 14:00:00', 'dd.mm.yyyy hh24:mi:ss')
);

insert into breaks values (
    3,
    2,
    to_timestamp('15.01.2024 13:30:00', 'dd.mm.yyyy hh24:mi:ss'),
    to_timestamp('15.01.2024 14:30:00', 'dd.mm.yyyy hh24:mi:ss')
);

commit;

Again, these have no resemblance with any real data, especially not mine.

Calculating break time

Calculating the total break time per day involves working with interval expressions as you can see:

SQL> select
  2      id,
  3      end_time - start_time
  4  from
  5*     breaks;

   ID END_TIME-START_TIME    
_____ ______________________ 
    1 +00 00:15:00.000000    
    2 +00 01:00:00.000000    
    3 +00 01:00:00.000000

The result of this particular calculation is represented as an INTERVAL DAY TO SECOND as explained in the documentation.

Up until Oracle Database 23c it wasn’t possible to pass these to the built-in sum() function. Trying to do so resulted in an error:

SQL> select 
  2      workday_id,
  3      sum(end_time - start_time)
  4  from breaks 
  5  group by 
  6*     workday_id;

Error starting at line : 1 in command -
select 
    workday_id,
    sum(end_time - start_time)
from breaks 
group by 
    workday_id
Error at Command Line : 3 Column : 18
Error report -
SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND
00932. 00000 -  "expression%s is of data type %s, which is incompatible with expected data type %s"
*Cause:    The input data type for this SQL operation was 
           incompatible with the expected data type.
*Action:   Modify the expression to have a compatible data type for the SQL operation.
*Params:   1) failed_expression type.
           2) expression_data_type: The data type of the column or expression.
           3) expected_data_type: The data type expected by the SQL operation.

More Details :
https://docs.oracle.com/error-help/db/ora-00932/

There are workarounds of course but they require additional typing and I’m inherently lazy. You can imagine that I was very happy to see this update in the SQL Language Reference for Oracle Database 23c:

Release 23c adds support for INTERVAL interval data types. However interval data types cannot be implicitly converted to a numeric data type. If the input is an INTERVAL, the function returns an INTERVAL with the same units as the input.

This is exactly what I needed! Calculating the cumulative break time per day is as easy as this:

SQL> select 
  2      to_char(start_time, 'dd.mm.yyyy') day,
  3      sum(end_time - start_time)
  4  from breaks 
  5  group by 
  6      day;

DAY           SUM(END_TIME-START_TIME)    
_____________ ___________________________ 
14.01.2024    +00 01:15:00.000000         
15.01.2024    +00 01:00:00.000000

Now we’re talking! So I spent 1 hour 15 minutes on breaks on the 14th of January, and 1 hour on the 15th.

There are actually 2 new features helping me write this query:

Support for interval data types for sum() – you read about that just now
The “group-by-alias” function allows me to group by day rather group by to_char(start_time, 'dd.mm.yyyy')

Summary

Oracle Database 23c aims at improving your productivity in many ways. There are (seemingly) little things like the one discussed in this post, and bigger features covered elsewhere making your life as a developer a lot easier.