AVG function with dates and intervals

[dwhalen]

Hello,

I have some sql that I want to port over from Postgresql to Oracle but I am having a lot of trouble with it. One thing in particular is using the AVG function with dates. Just to give you an example of what I am tyring to do, in postgresql I use the AGE function to find out the interval between the two dates ex: AGE(creation_date,close_date) but then in a summary query I use AVG(AGE(creation_date,close_date)) which will average all the intervals. How can I do this in Oracle? Here is a small snippet of my sql:

SELECT org,workbasket,task,
AVG(((close_date - creation_date) DAY TO SECOND)),COUNT(task_id)
FROM perfor_view_distinct
GROUP BY org,workbasket,task;

I get an error saying:
ERROR at line 1:
ORA-00932: inconsistent datatypes

Do I have to cast the interval into another datatype? If so, how do I do that?

Thanks for any help you can give.

Later

P.S.

I am using Oracle 9i.

 

[ManjulaMadhu]

Hi,

Presuming that both the dates are of the same datatype(Date), it is not necessary for casting them.
By the way, DAY TO SECOND in the query ? If its an alias , then wrap it in quotes like

SELECT org,workbasket,task,
AVG(close_date - creation_date) "DAY TO SECOND",COUNT(task_id)
FROM perfor_view_distinct
GROUP BY org,workbasket,task;

 

[sem]

I suppose the only "casting" you need is multiplying date difference by 24*60*60. Oracle supports explicit subtracting of dates and returns the result in days.

Regards, Dima

 

[dwhalen]

I guess you guys are using 8i or below because in 9i the DAY TO SECOND is converting the return into an interval, but I found out I don't need that because the two dates are of Timestamp data type which return an interval when you subtract them. So AVG(close_date - creation_date) will not work because of inconsistent datatypes. Does any one know how to deal with Timestamps and intervals in this manner?

Thanks for your help

Later

 

[sem]

I'd sugest you to ask 9i-related questions in 9i forum . Why don't you cast them into DATE first?

Regards, Dima

 

[dwhalen]

Sorry, didn't know there was a 9i form. And I just figured it out; it is not pretty but it works. Here is the SQL in case someone has the same problem:

This is how to AVG timestamp values:

select AVG((TO_DATE(TO_CHAR(close_date,'DD-MON-YY HH:MI:SS'),'DD-MON-YY HH:MI:SS') - TO_DATE(TO_CHAR(creation_date,'DD-MON-YY HH:MI:SS'),'DD-MON-YY HH:MI:SS'))) from perfor_view_distinct;
[\code]

Hopefully Oracle will provide better functions for there new datatypes so we don't have to do this!  Or maybe there is a better way and I just don't know it yet.

Later