如何平均时间间隔? [英] How to average time intervals?

问题描述

在Oracle 10g中，我有一个表，其中包含显示某些操作花费了多长时间的时间戳记.它有两个时间戳字段:开始时间和结束时间.我想找到这些时间戳记给出的持续时间的平均值.我尝试:

In Oracle 10g I have a table that holds timestamps showing how long certain operations took. It has two timestamp fields: starttime and endtime. I want to find averages of the durations given by these timestamps. I try:

select avg(endtime-starttime) from timings;

但是得到:

SQL错误:ORA-00932:不一致 数据类型:预期获得NUMBER个 第二天的间隔

SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND

这有效:

select
     avg(extract( second from  endtime - starttime) +
        extract ( minute from  endtime - starttime) * 60 +
        extract ( hour   from  endtime - starttime) * 3600) from timings;

但是真的很慢.

有什么更好的方法可以将间隔转换为秒数，或者通过其他某种方式呢?

Any better way to turn intervals into numbers of seconds, or some other way do this?

真正减慢速度的原因是我在开始时间之前有一些结束时间.由于某种原因，这使计算变得异常缓慢.通过从查询集中消除它们，解决了我的基本问题.我还只是定义了一个函数来简化此转换:

What was really slowing this down was the fact that I had some endtime's before the starttime's. For some reason that made this calculation incredibly slow. My underlying problem was solved by eliminating them from the query set. I also just defined a function to do this conversion easier:

FUNCTION fn_interval_to_sec ( i IN INTERVAL DAY TO SECOND )
RETURN NUMBER
IS
  numSecs NUMBER;
BEGIN
  numSecs := ((extract(day from i) * 24
         + extract(hour from i) )*60
         + extract(minute from i) )*60
         + extract(second from i);
  RETURN numSecs;
END;

推荐答案

最干净的方法是编写自己的聚合函数来执行此操作，因为它将最干净地处理此问题(处理亚秒级分辨率等).

The cleanest way is to write your own aggregate function to do this, since it will handle this the most cleanly (handles sub-second resolution, etc.).

实际上，是在

In fact, this question was asked (and answered) on asktom.oracle.com a while back (article includes source code).

这篇关于如何平均时间间隔?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！