CODEWITHSUNDEEP
sqloracle-databaseoracle11gtimestamp
YK mar
YK mar

Reputation: 677

Average Timestamp oracle with milliseconds

Hello everyOne I need to get the AVERAGE of difference of two dates (timestamp)

I tried this

select AVG((sva.endTime - sva.startTime)) as seconds from SVATable sva;

but I got an error

93/5000
ORA-00932: Inconsistent data types; expected: NUMBER; got: INTERVAL DAY TO SECOND

Upvotes: 1

Views: 1932

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

You may use EXTRACT to get AVG seconds.

SELECT AVG (EXTRACT (SECOND FROM (sva.endTime - sva.startTime)))
          AS avg_seconds
  FROM SVATable sva;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269543

This is an insidious problem in Oracle. Your calculation would work with the date data type, but it does not work with timestamps.

One solution is to extract the days, hours, minutes, and seconds from the interval. Another is to use date arithmetic. You can get fractions of a day by using:

select (date '2000-01-01' + (sva.endTime - sva.startTime)) - date '2000-01-01'

You can use the average and convert to seconds:

select avg( (date '2000-01-01' + (sva.endTime - sva.startTime)) - date '2000-01-01') * (60*60*24)

Upvotes: 1

Related Questions