Averaging A List Of Timestamp(6) With Time Zone Times

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-impor

Solution 1:

Use the AVG function

SELECTavg(cast(lastprocesseddate asdate)-cast(importeddate asdate))
FROM feedqueueitems 
WHERE eventid =2213283ORDERBY written DESC;

On the Database with the +1 timezone for importeddate and lastprocesseddate is UTC

SELECTavg(cast(cast(lastprocesseddate astimestampwithtime zone) attime zone '+01:00'asdate)-cast(importeddate asdate))
FROM feedqueueitems 
WHERE eventid =2213283ORDERBY written DESC;

Solution 2:

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:

selectavg(extract(secondfrom gap)
    +extract(minutefrom gap) *60+extract(hourfrom gap) *60*60+extract(dayfrom gap) *60*60*24) as avg_gap
from (
  select lastprocesseddate-importeddate as gap
  from feedqueueitems
  where eventid =2213283
);

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

with cte as (
  selectinterval'+00 00:00:00.488871'daytosecondas gap from dual
  unionallselectinterval'+00 00:00:00.464286'daytosecondfrom dual
  unionallselectinterval'+00 00:00:00.477107'daytosecondfrom dual
  unionallselectinterval'+00 00:00:00.507042'daytosecondfrom dual
  unionallselectinterval'+00 00:00:00.369144'daytosecondfrom dual
  unionallselectinterval'+00 00:00:00.488918'daytosecondfrom dual
  unionallselectinterval'+00 00:00:00.354797'daytosecondfrom dual 
  unionallselectinterval'+00 00:00:00.378801'daytosecondfrom dual
  unionallselectinterval'+00 00:00:00.320040'daytosecondfrom dual
  unionallselectinterval'+00 00:00:00.361242'daytosecondfrom dual
  unionallselectinterval'+00 00:00:00.302327'daytosecondfrom dual
  unionallselectinterval'+00 00:00:00.331441'daytosecondfrom dual
  unionallselectinterval'+00 00:00:00.324065'daytosecondfrom dual
)
selectavg(extract(secondfrom gap)
    +extract(minutefrom gap) *60+extract(hourfrom gap) *60*60+extract(dayfrom gap) *60*60*24) as avg_gap
from cte;

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

Or if you wanted it as an interval:

select numtodsinterval(avg(extract(secondfrom gap)
    +extract(minutefrom gap) *60+extract(hourfrom gap) *60*60+extract(dayfrom gap) *60*60*24), 'SECOND') as avg_gap
...

which gives

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

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

Solution 3:

This query should solve the issue.

WITH t AS 
    (SELECTTIMESTAMP'2015-04-23 12:00:00.5 +02:00'AS lastprocesseddate,  
        TIMESTAMP'2015-04-23 12:05:10.21 UTC'AS importeddate 
    FROM dual)
SELECTAVG(
        EXTRACT(SECONDFROM SYS_EXTRACT_UTC(lastprocesseddate) - SYS_EXTRACT_UTC(importeddate))
        +EXTRACT(MINUTEFROM SYS_EXTRACT_UTC(lastprocesseddate) - SYS_EXTRACT_UTC(importeddate)) *60+EXTRACT(HOURFROM SYS_EXTRACT_UTC(lastprocesseddate) - SYS_EXTRACT_UTC(importeddate)) *60*60+EXTRACT(DAYFROM SYS_EXTRACT_UTC(lastprocesseddate) - SYS_EXTRACT_UTC(importeddate)) *60*60*24
    ) AS average_gap
FROM t;