Averaging Dates in Oracle SQL

Averaging dates in oracle sql

The definition of an "average date" is subjective, but you could convert your dates to a Julian number, then average those, round it off, then convert back to a date.

create table dates (dt DATE);

insert into dates 
values ('24-APR-2012');
insert into dates 
values ('01-JAN-2012');
insert into dates 
values ('01-JAN-2013');
insert into dates
values ('25-DEC-1900');

select to_date(round(avg(to_number(to_char(dt, 'J')))),'J')
from dates;

Here's the SQL Fiddle: http://sqlfiddle.com/#!4/98ce9/1

Average Date column in a query using Oracle SQL Developer v19

Indeed a window function function is probably the what you are after, as Gordon Linoff points out. Using standard joins is also a must-have.

I would, however, recommend also fixing the date arithmetic; not all years have 365 days, so your query is off on leap years - and inaccuracy increases when a person gets older. Instead, I would recommend months_between():

select p.first_name, p.last_name, p.sex, p.ethnicity, 
    round(months_between(sysdate, p.d_o_b) / 12, 0) as age,
    round(avg(months_between(sysdate, p.d_o_b) / 12) over(), 0) as avg_age
from patient p 
inner join tests t on t.patient_id = p.nhs_number
where t.result = 'positive' and t.date_of_test + 14 > sysdate
group by p.first_name, p.last_name, p.sex, p.ethnicity, p.d_o_b
order by age desc;

Oracle average difference between two date fields

Here's an example.

When subtracting two date datatype values, the result is number of days. It shows the INTER CTE. When you multiply it by 24 (number of hours in a day), 60 (number of minutes in an hour) and 60 (number of seconds in a minute), the result is number of seconds (DIFF_SECS).

AVERAGES CTE shows how to apply AVG function to previous results; nothing special in that, just pay attention that you have to GROUP it BY the UNIT column.

Finally, apply TO_CHAR formatting to calculation (some TRUNC and MOD calls in order to extract hours, minutes and seconds from the AVG_DIFF_SECS value).

I suggest you run each CTE separately, step by step, to easier follow the execution.

SQL> with test (unit, arr, dep) as
  2    (select 1, to_date('27.01.2017 08:01:20', 'dd.mm.yyyy hh24:mi:ss'),
  3               to_date('27.01.2017 08:04:27', 'dd.mm.yyyy hh24:Mi:ss')
  4     from dual union all
  5     select 1, to_date('27.01.2017 08:05:35', 'dd.mm.yyyy hh24:mi:ss'),
  6               to_date('27.01.2017 08:09:28', 'dd.mm.yyyy hh24:Mi:ss')
  7     from dual
  8    ),
  9  inter as
 10    (select unit, (dep - arr) diff_days,
 11       (dep - arr) * 24 * 60 * 60 diff_secs
 12     from test
 13    ),
 14  averages as
 15    (select unit,
 16       avg(dep - arr) avg_diff_days,
 17       avg((dep - arr) * 24 * 60 * 60) avg_diff_secs
 18     from test
 19     group by unit
 20    )
 21  select
 22    to_char(trunc(avg_diff_secs / 3600), 'fm00') || ':' ||           -- hours
 23    to_char(trunc(mod(avg_diff_secs , 3600) / 60), 'fm00') || ':' || -- minutes
 24    to_char(mod(avg_diff_secs, 60), 'fm00')                          -- seconds
 25    avg_diff_formatted
 26  from averages;

AVG_DIFF_FORMATTED
--------------------
00:03:30

SQL>

avg time oracle sql

To compute an average time, you would use :

SELECT
    job_nm,
    TO_CHAR(
        TO_DATE(
            AVG(
                TO_NUMBER(
                    TO_CHAR(
                        TO_DATE(
                            TO_CHAR(Start_Dt,'HH24:MI:SS'),
                        'HH24:MI:SS'),
                    'sssss')
                )
            ),
        'sssss'),
    'hh24:mi:ss')
FROM batch_table
WHERE Start_Dt >= trunc(sysdate-10)
GROUP BY job_nm;

Here is another option, maybe less convoluted :

 SELECT 
    job_nm, 
    FLOOR(24 * AVG(Start_Dt- TRUNC(Start_Dt)))
     || ':'
     || FLOOR(MOD(24 * AVG(Start_Dt - TRUNC(Start_Dt)),1) * 60)
     || ':'
     || FLOOR(MOD(MOD(24 * AVG(Start_Dt- TRUNC(Start_Dt)),1) * 60,1) * 60)
FROM batch_table
WHERE Start_Dt >= trunc(sysdate-10)
GROUP BY job_nm;

And, just in case, if you are looking to compute an average date, you would convert dates to Julian date format, compute average, then translate back to date, like :

SELECT
    job_nm,
    TO_DATE(
        ROUND(
            AVG(
                TO_NUMBER(TO_CHAR(Start_Dt, 'J'))
            )
        ),
   'J')
FROM batch_table
WHERE Start_Dt >= trunc(sysdate-10)
GROUP BY job_nm;

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>

How to get the average in pl/sql from a count within multiplel dates

One method is a subquery:

SELECT AVG(NRO_REGISTROS)
FROM (SELECT FECHAGENERACION, IDAPLICACION, COUNT(*) as NRO_REGISTROS
      FROM ADMBLOQMON.BOC_MAEBLOQAUX_BLOQ_MONO 
      WHERE fechageneracion between DATE '2020-01-12' AND DATE '2020-03-12'  AND
            IDAPLICACION = 01
      GROUP BY FECHAGENERACION, IDAPLICACION
     ) FI;

Another uses COUNT(DISTINCT) -- but assumes that the two keys are never NULL and can be separated unambiguously:

SELECT COUNT(*) / COUNT(DISTINCT FECHAGENERACION || '|' ||  IDAPLICACION)
FROM ADMBLOQMON.BOC_MAEBLOQAUX_BLOQ_MONO 
WHERE fechageneracion between DATE '2020-01-12' AND DATE '2020-03-12' AND
      IDAPLICACION = 01;

And a third method would add an additional column to the existing data set:

SELECT COUNT(*) / COUNT(DISTINCT FECHAGENERACION || '|' ||  IDAPLICACION),
       SUM(COUNT(*)) / COUNT(*) OVER () as overall_avg
FROM ADMBLOQMON.BOC_MAEBLOQAUX_BLOQ_MONO 
WHERE fechageneracion between DATE '2020-01-12' AND DATE '2020-03-12' AND
      IDAPLICACION = 01;

Averaging Dates in Oracle SQL