Calculating aggregates on a timestamp

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

gives:
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.

So:

select avg(
(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

works fine

Leave a Reply

Your email address will not be published. Required fields are marked *