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!
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 .
{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.
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.
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
)
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With