Calculate average time between two datetimes across all records?

Question

Each record has a started_at and completed_at datetime field. To calculate the time it took someone to start and complete is obviously easy.

But what I need to do is calculate an average time from start to complete for ALL records.

Ultimately want to be able to say "It takes, on average, X amount of time to start and complete."

I'm running Rails 3.0.6, in case there's some functionality already built in with that.

Also, it's a Postgresql database, but ideally this would work across other databases.

Answer 1

A Pure SQL solution would be:

SELECT AVG(TotTime)
FROM (SELECT DateDiff(minute, started_at, completed_at) as 'TotTime'
      FROM MyTable
      WHERE <stuff>) as SubQuery

You can change the part in the DateDiff function to be whatever you need (hours, seconds, milliseconds, etc).