Sokrates on Oracle

Average of DATEs

Posted by Matthias Rogel on 7. June 2011

avg does not support DATEs as arguments:

sokrates@11.2 > select avg(created) from dba_users;
select avg(created) from dba_users
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE

Why ? I don’t know

sokrates@11.2 > select
2 min( created ),
3 max( created ),
4 median( created )
5 from dba_users;
MIN(CREATED) MAX(CREATED) MEDIAN(CREATED)
-------------------- -------------------- --------------------
17-sep-2010 16:08:04 04-apr-2011 11:13:54 09-oct-2010 15:57:09

work without problems.

So, what’s the easiest workaround when you need avg of DATEs ?

sokrates@11.2 > select
2 sysdate - avg ( sysdate - created ) "avg(created)"
3 from dba_users;
avg(created)
--------------------
15-oct-2010 21:19:00

is neat, isn’t it ?

And: it’s deterministic (doesn’t depend on SYSDATE) !
(proof of that is left to the reader)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: