用TIME ZONE时间平均TIMESTAMP(6)的列表 [英] Averaging a list of TIMESTAMP(6) WITH TIME ZONE times

问题描述

我在TIMESTAMP(6)WITH TIME ZONE类型的数据库中有2列.我从另一个减去一个以获得两个时间戳之间的时间.

I've got 2 columns in a database of type TIMESTAMP(6) WITH TIME ZONE. I've subtracted one from the other to get the time between the two timestamps.

select lastprocesseddate-importeddate 
from feedqueueitems 
where eventid = 2213283 
order by written desc;

如何获取我所拥有的时差列表的平均值?

How can I get an average of the list of time differences I have?

以下是时差的一小部分:

Here are a small sample of time differences:

+00 00:00:00.488871    
+00 00:00:00.464286  
+00 00:00:00.477107  
+00 00:00:00.507042  
+00 00:00:00.369144  
+00 00:00:00.488918  
+00 00:00:00.354797  
+00 00:00:00.378801  
+00 00:00:00.320040  
+00 00:00:00.361242  
+00 00:00:00.302327  
+00 00:00:00.331441  
+00 00:00:00.324065

我也应该注意-我已经尝试了AVG函数，它只是返回

I also should have noted - I've tried the AVG function, and it just returns

ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
Error at Line: 3 Column: 29

只是为了澄清上面的片段.第3行是我的SQL查询，全部以以下格式显示在一行上:

Just to clarify the above snippet. Line 3 is my SQL query all on one line in the following format:

select AVG(lastprocesseddate-importeddate) from feedqueueitems where eventid = 2213283;

非常感谢Matt和Alex Poole.你们俩都提供了大量帮助，我非常感谢您抽出宝贵的时间为您提供帮助，并希望他们一如既往地提供最新的帮助，以回应反馈/其他问题！谢谢大家！

Massive thanks to Matt and Alex Poole. You've both helped massively and I appreciate you taking the time to help with this and to both consistently return with updated help in response to the feedback/further problems! Thanks guys!

推荐答案

您可以从每个间隔值类型(间隔数据类型)中提取时间分量，因此最终得到以秒为单位的数字(包括小数部分) ，然后取平均值:

You could extract the time components from each gap value, which is an interval data type, so you end up with a figure in seconds (including the fractional part), and then average those:

select avg(extract(second from gap)
    + extract(minute from gap) * 60
    + extract(hour from gap) * 60 * 60
    + extract(day from gap) * 60 * 60 * 24) as avg_gap
from (
  select lastprocesseddate-importeddate as gap
  from feedqueueitems
  where eventid = 2213283
);

使用CTE提供您显示的间隔值的演示:

A demo using a CTE to provide the interval values you showed:

with cte as (
  select interval '+00 00:00:00.488871' day to second as gap from dual
  union all select interval '+00 00:00:00.464286' day to second from dual
  union all select interval '+00 00:00:00.477107' day to second from dual
  union all select interval '+00 00:00:00.507042' day to second from dual
  union all select interval '+00 00:00:00.369144' day to second from dual
  union all select interval '+00 00:00:00.488918' day to second from dual
  union all select interval '+00 00:00:00.354797' day to second from dual 
  union all select interval '+00 00:00:00.378801' day to second from dual
  union all select interval '+00 00:00:00.320040' day to second from dual
  union all select interval '+00 00:00:00.361242' day to second from dual
  union all select interval '+00 00:00:00.302327' day to second from dual
  union all select interval '+00 00:00:00.331441' day to second from dual
  union all select interval '+00 00:00:00.324065' day to second from dual
)
select avg(extract(second from gap)
    + extract(minute from gap) * 60
    + extract(hour from gap) * 60 * 60
    + extract(day from gap) * 60 * 60 * 24) as avg_gap
from cte;

   AVG_GAP
----------
.397544692

或者如果您希望将其作为间隔:

Or if you wanted it as an interval:

select numtodsinterval(avg(extract(second from gap)
    + extract(minute from gap) * 60
    + extract(hour from gap) * 60 * 60
    + extract(day from gap) * 60 * 60 * 24), 'SECOND') as avg_gap
...

给出

AVG_GAP            
--------------------
0 0:0:0.397544692   

SQL提琴，只需几秒钟即可得到答案. (目前似乎不喜欢显示间隔，因此无法演示.)

SQL Fiddle with answer in seconds. (It doesn't seem to like displaying intervals at the moment, so can't demo that).

这篇关于用TIME ZONE时间平均TIMESTAMP(6)的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！