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
