getting an interval in hours

[dwhalen]

Hello,

I am having some problems with date and time functions. What I am doing is finding the average time it takes to complete a particular task in a group. To do this I am using this sql:

SELECT date_trunc('second',AVG(AGE(close_date,creation_date))) from performance_view WHERE Workbasket iLike 'Sales' AND task = 'Sales Confirmation';

This works great, I get a result like 3 days 2 hours and whatever. The problem is the workbasket group, let us say Sales has multiple tasks in it. What I want to do is do a final Avg of all the tasks in each workbasket. I don't think I can do this in my SQL so I was going to loop through the result and add up the intervals and then divide by the total. The problem is you can't add 2 days 3 hours with 4 days 6 hours in perl! So what I would like to do is to retrieve another value from the database but have this value be in one type of number...what I mean is 2 days and 3 hours returned as 51 hours. This way I can just add up the numbers and divide by the total. Is there a way I can do this. Here is some of the things I was trying:

SELECT date_trunc('second',AVG(AGE(close_date,creation_date)))/24 from performance_view where workbasket iLike 'Sales';

or

SELECT date_trunc('second',AVG(AGE(close_date,creation_date)))/ interval '24 hours' from performance_view where workbasket iLike 'Sales';

or

SELECT date_trunc('second',AVG(AGE(close_date,creation_date)))::interval('hours') AS g from performance_view where workbasket iLike 'Sales';

Thanks for any suggestions. I hope I was clear on what I want.

Later