Take the 2-minute tour ×

I don't see hwo this is that ahrd, but I can't seem to find the solution anywhere. It's done for dates, but I can't see to make it work for TIMESTAMP.

I'm trying to do

select avg(last_timestmp - ref_timestmp) as average from param

It keeps telling me it's not a valid number, which I get. But how do I make it a valid number? I have tried extract and a bunch of other stuff but nothing seems to work.

I want the average in seconds. one hundredth of a second would be .01 and 6 hours would be 21600


share|improve this question

2 Answers 2

You might try

SELECT AVG(p.last_date - p.ref_date) as average FROM (
    last_timestamp - TO_DATE('1970-01-01', 'YYYY-MM-DD') \* 8640000 as last_date
    ,ref_timestamp - TO_DATE('1970-01-01', 'YYYY-MM-DD') \* 8640000 as ref_date
  FROM param ) p

This will give you the difference in milliseconds.

See: http://blogs.oracle.com/mock/entry/converting_oracle_dates_to_unix

share|improve this answer
inconsistent datatypes: expected NUMBER got DAT –  kralco626 Oct 4 '11 at 16:35

You can use EXTRACT to get out the parts as seconds and add them up then calculate your average:

    avg(extract(second from intrvl)
        + extract(minute from intrvl) * 60
        + extract(hour from intrvl) * 60 * 60
        + extract(day from intrvl) * 60 * 60 * 24) average
from (
    select (last_timestmp - ref_timestmp) intrvl 
    from param
share|improve this answer

Your Answer


