Date averaging in oracle sql

Question

Is there a way to average multiple dates in an oracle? avg does nothing good.

Thank.

Answer 1

The definition of "average date" is subjective, but you can convert your dates to a Julian number, then average them, round them, then convert them 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

Answer 2

The median function in the oracle neatly does exactly what the other answers do.

Here is the link to the Oracle documentation - https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions086.htm

arg , .

SQL> desc x1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
A                                         NOT NULL NUMBER
D                                                  DATE

SQL> select * from x1;

     A D
---------- ---------
     1 11-DEC-14
     2 13-DEC-14
     3 22-DEC-14
     4 02-DEC-14
SQL> select median(d) from x1;

MEDIAN(D)
---------
12-DEC-14

Answer 3

Oracle , , TRUNC (SYSDATE) + 1 .

, :

1) (, SYSDATE)

2) , ,

3) SYSDATE .

, ( dt )

 TO_DATE(
    TRUNC(SYSDATE) - ROUND(AVG(TRUNC(SYSDATE) - TRUNC(dt)))
 )

According to some exemplary data, this was done a little less than half the time of Dan A.'s approach above and produced the same result. I only checked it for data with dates in the past, but I see no obvious reason that it will not generalize (the last known words when working with DATETIME data, I understand ...)

Answer 4

SYSDATE + AVG( dt - SYSDATE )

No need to convert date to date. Truncate the entire expression if you do not want time to be included in the result. Truncate the date column (dt) if you do not want the time to be used in calculating the average. MEDIAN is not the same as AVG.