Need to Find Average Processing Time Between All Timestamp Records in Oracle SQL

oracle pl/sql average from date difference

When you subtract two DATE datatype values, result is number of days. For example (based on Scott's schema):

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> select deptno,
  2    hiredate,
  3    lag(hiredate) over (partition by deptno order by hiredate) lag_hiredate,
  4    --
  5    hiredate - lag(hiredate) over
  6               (partition by deptno order by hiredate) diff
  7  from emp
  8  order by deptno, hiredate;

    DEPTNO HIREDATE   LAG_HIREDA       DIFF
---------- ---------- ---------- ----------
        10 09.06.1981
        10 17.11.1981 09.06.1981        161
        10 23.01.1982 17.11.1981         67
        20 17.12.1980
        20 02.04.1981 17.12.1980        106
        20 03.12.1981 02.04.1981        245
        30 20.02.1981
        30 22.02.1981 20.02.1981          2
        30 01.05.1981 22.02.1981         68
        30 08.09.1981 01.05.1981        130
        30 28.09.1981 08.09.1981         20
        30 03.12.1981 28.09.1981         66

12 rows selected.

SQL>

If you want to select average difference, you'll have to use an inline view or CTE as AVG and analytic function can't be used at the same time, i.e. avg(lag(...)).

Finally, as you need number of minutes, multiply the result (days, right?) by 24 (as there are 24 hours in a day) and 60 (as there are 60 minutes in an hour):

SQL> with inter as
  2    (select deptno,
  3            hiredate - lag(hiredate) over
  4                       (partition by deptno order by hiredate) diff
  5     from emp
  6  )
  7  select deptno,
  8    avg(diff) avg_diff_days,
  9    --
 10    avg(diff) * (24 * 60) minutes
 11  from inter
 12  group by deptno;

    DEPTNO AVG_DIFF_DAYS    MINUTES
---------- ------------- ----------
        10           114     164160
        20         175,5     252720
        30          57,2      82368

SQL>

---

[EDIT: added timestamp example]

SQL> create table test (datum timestamp);

Table created.

SQL> select * From test;

DATUM
---------------------------------------------------------------------------
04.06.18 08:57:34,000000
04.06.18 09:34:34,000000
04.06.18 09:34:34,000000

SQL>
SQL> select datum - lag(datum) over (order by datum) diff
  2  from test;

DIFF
---------------------------------------------------------------------------

+000000000 00:37:00.000000
+000000000 00:00:00.000000

SQL> -- cast timestamps to dates first, then subtract them; for the final result,
SQL> -- multiply number of days by 24 hours (in a day) and 60 minutes (in an hour)
SQL> select avg(diff) * 24 * 60 avg_minutes
  2  from (select cast(datum as date) - cast(lag(datum) over (order by datum) as date) diff
  3        from test
  4       );

AVG_MINUTES
-----------
       18,5

SQL>

Counting number of records hour by hour between two dates in oracle

Note the usage of trunc expression with date values. You can omit the alter session if you are not running the query in sql*plus.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> SELECT 
       trunc(created,'HH'), 
       count(*) 
     FROM 
       test_table 
     WHERE 
       created > trunc(SYSDATE -2) 
     group by trunc(created,'HH');

TRUNC(CREATED,'HH')   COUNT(*)
------------------- ----------
2012-05-21 09:00:00        748
2012-05-21 16:00:00         24
2012-05-21 17:00:00         12
2012-05-21 22:00:00        737
2012-05-21 23:00:00        182
2012-05-22 20:00:00         16
2012-05-22 21:00:00        293
2012-05-22 22:00:00        610

8 ROWS selected.

SQL Moving Average over specific time

This is a little tricky. Although Hive supports range window frames, it only supports them with numbers, not intervals.

So, you need to convert the timestamp to a number and then use that:

select t.*,
       avg(value) over (order by unix_timestamp(timestamp)
                        range between 3559 preceding and current row
                       )
from t;

3559 = 60 * 60 - 1, which is one second less than an hour. One second less is used because the window frame includes the current row.

How to find average on a timestamp column

Demo: http://sqlfiddle.com/#!9/33c09/3

Statement:

select TIME_FORMAT(avg(cast(startDate as time)),'%h:%i:%s %p') as avg_start_date
from demo

Setup:

create table demo (startDate datetime);

insert demo (startDate) values ('2015-04-10 3:46:07');
insert demo (startDate) values ('2015-04-09 3:47:37');
insert demo (startDate) values ('2015-04-08 3:48:07');
insert demo (startDate) values ('2015-04-07 3:43:44');
insert demo (startDate) values ('2015-04-06 3:39:08');
insert demo (startDate) values ('2015-04-03 3:47:50');

Explanation:

• Casting to Time ensures the Date component is ignored (if you're averaging datetimes and hoping for an average time it's like averaging double figure numbers and hoping for the unit to match what you'd have seen if you'd only averaged the units of those numbers).
• AVG is the average function you're already familiar with.
• TIME_FORMAT is to present your data in a user friendly way so you can check your results.

---

Related Topics