Today I needed to use aggregates on the Oracle TIMESTAMP datatype.
We had a table DTM_MESSAGES with the columns received and responded,
both with datatype timestamp.
This table is used to measure the responsetime of a webservice.
When we substract these 2 columns with the query:
select received- responded from dtm_messages
We get a time-interval as response, not a number.
Because of this we can not use aggregate functions like sum or avg on the
difference between 2 timestamps.
select avg(received- responded) from dtm_messages
ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL TO SECOND.
If we want to use an aggregate function, we need to convert this all to seconds.
Then we can use an aggregate function again.
(extract(day from responded)-extract(day from received))*86400
+ (extract(hour from responded)-extract(hour from received))*3600
+ (extract(minute from responded)-extract(minute from received))*60 + (extract(second from responded) -extract(second from received))) avg_seconds from dtm_messages