Forum OpenACS Q&A: Oracle date/time interval fails with sum()

I've always used Oracle's traditional and non-standard "date" type. Recently, I had to write queries using Oracle's new "standard" timestamp and interval date/time types, and let me tell you, they really suck!

The principle problem is that type conversion for these types seems to be very broken. In particular, for interval types, nNot only is there no automatic type conversion at all, AFAICT there isn't even any manual conversion! Philip noticed this back in 2003. The exact same problems still exist years later in 10gR2. Oracle's cast() function just plain does not work for many of these newfangled date/time types. If you have a timestamp, you can convert it to a string and then to a date, that does work. But if you have a table of intervals, AFAICT you cannot do a sum() over those rows in any reasonable way.

Tom Kyte gave an ugly solution, which is to write your own aggregate function using ODCIAggregateIterate(). Why it's necessary to do that for native built-in Oracle types, I have no idea.

The practical work-around seems to be to convert any newfangled timestamp types to old-style dates (via strings) as early as possible, before any date/time intervals get involved. Of course, then you're restricted to 1 second precision, and AFAIK getting better than 1 s precision is the only real reason to use timestamps in the first place.

Some examples illustrating the above:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select  1 as id  ,(interval '0 00:00:01' day to second) as sec  from all_tables  where rownum <= 3;

        ID SEC
---------- --------------------
         1 +00 00:00:01.000000
         1 +00 00:00:01.000000
         1 +00 00:00:01.000000

SQL> select id ,sum(sec)  from (
select  1 as id  ,(interval '0 00:00:01' day to second) as sec  from all_tables  where rownum <= 3
) group by id ;
               *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND

SQL> select  1 as id
  ,cast((interval '0 00:00:01' day to second) as number) as sec
from all_tables  where rownum <= 3 ;
         *
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND

SQL> select  1 as id
  ,cast((interval '0 00:00:01' day to second) as date) as sec
from all_tables  where rownum <= 3 ;
         *
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND