postgresql find the average time between 2 or more date

Question

i have a table tbltrans

transno | agent name | date|

--------+-----------+-------

 1 01   | waw   |2014-10-10|

 1 02   | waw   |2014-10-15|

 1 03   | waw   |2014-10-20|

 1 04   | waw   |2014-10-30|

i want a query for postgresql that calculate the average time for agentname

the result is like this:

agent name | average time|

--------+------------

 waw      | 5days      |

i hope somebody help me. . thanks and more power :)

Answer 1

Something like this:

select agent_name, 
       (max(date) - min(date)) / count(*) as average_days
from tbltrans
group by agent_name;

Answer 2

Here is the solution: B-)

with diff(agent_name, values) as 
(
 select agent_name, date - lag(date,1,date) over (partition by agent_name order by date) from agents
)
select agent_name, 
       case when count(values) = 1 then '0 days'
            else to_char(sum(values)::float / (count(values) - 1), '999.99') || ' days'
       end
from diff
group by agent_name;

I used Windowing functions and Common table expressions in the solution