oracle pl/sql average from date difference

Question

I need to get the average from a date from this query

select service_id,t.arrival_date - 
       lag(t.arrival_date) over 
          (partition by t.service_id order by t.arrival_date) as arrival_date_diff
from table t

And I get this data as result

+000000000 00:37:00.000000
+000000000 00:23:30.000000
+000000000 00:07:04.000000

I tried to use

AVG(arrival_date_diff) to_date(round(avg(to_number(to_char(arrival_date_diff, 'J')))),'J')

How can I get the average from this date difference value and convert the average date to minutes?

Sample data:

04/06/18 08:57:34,000000
04/06/18 09:34:34,000000
04/06/18 09:34:34,000000

Expected result: average in minutes(for example: 8.5)

Thanks in advance

Could you provide some sample data and expect result? that really help — D-Shih, Jun 19 '18 at 18:27
This may help https://stackoverflow.com/questions/1096853/calculate-difference-between-2-date-times-in-oracle-sql — Juan Carlos Oropeza, Jun 19 '18 at 18:39
you can create a custom aggregate to do this for timestamps, see [this SO post](https://stackoverflow.com/questions/21001583/need-to-find-average-processing-time-between-all-timestamp-records-in-oracle-sql/21003402#21003402) — tbone, Jun 19 '18 at 20:32

Littlefoot · Accepted Answer · 2018-06-19T19:10:04.187

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>

I get the error `ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND` — Joseleg, Jun 19 '18 at 18:47
It looks like OP is getting an `interval` back. I'm guessing their `arrival_date` field is a timestamp, not a date, in which case they'll have to [convert it to a date](https://stackoverflow.com/questions/37559741/convert-timestamp-to-date-in-oracle-sql) before this will work. — kfinity, Jun 19 '18 at 18:47
Right; thank you for the comment and useful link for Joseleg, @kfinity. Scott doesn't have any *timestamps* so I used what I had. — Littlefoot, Jun 19 '18 at 18:50

oracle pl/sql average from date difference

1 Answers1