Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle find average of two timestamps

Tags:

sql

oracle

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

Thanks!

like image 864
kralco626 Avatar asked Oct 04 '11 16:10

kralco626


People also ask

How can I find the difference between two timestamps in Oracle?

To calculate the difference between the timestamps in Oracle, simply subtract the start timestamp from the end timestamp (here: arrival - departure ). The resulting column will be in INTERVAL DAY TO SECOND . The first number you see is the number of whole days that passed from departure to arrival .

How do I subtract two timestamps in SQL?

{fn TIMESTAMPDIFF(interval,startDate,endDate)} returns the difference between the starting and ending timestamps (startDate minus endDate) for the specified date part interval (seconds, days, weeks, and so on). The function returns an INTEGER value representing the number of intervals between the two timestamps.

Can we use datediff in Oracle?

Use the @DATEDIFF function to calculate the difference between two dates or datetimes, in days or seconds. The difference between the specified dates. Valid values can be: DD , which computes the difference in days.


2 Answers

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

select
    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
)
like image 81
Craig Avatar answered Sep 20 '22 11:09

Craig


You might try

SELECT AVG(p.last_date - p.ref_date) as average FROM (
  SELECT 
    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

like image 44
Johan Avatar answered Sep 24 '22 11:09

Johan