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;
-------------------- -------------------- --------------------
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;
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)


Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: