Oracle 找到两个时间戳的平均值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7650782/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle find average of two timestamps
提问by kralco626
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.
我不认为这是 ahrd，但我似乎无法在任何地方找到解决方案。它是针对日期完成的，但我看不到它是否适用于 TIMESTAMP。
I'm trying to do
我正在尝试做
select avg(last_timestmp - ref_timestmp) as average from param
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 .01and 6 hours would be 21600
我想要以秒为单位的平均值。百分之一秒是.016 小时是21600
Thanks!
谢谢！
回答by Craig
You can use EXTRACT to get out the parts as seconds and add them up then calculate your average:
您可以使用 EXTRACT 以秒为单位提取零件并将它们相加，然后计算您的平均值：
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
)
回答by Johan
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
请参阅：http: //blogs.oracle.com/mock/entry/converting_oracle_dates_to_unix
