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.
每个记录都有一个started_at和completed_at datetime字段。计算某人开始和完成的时间显然很容易。
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."
最终，我们希望能够说:“平均来说，开始和完成的时间是X。”
I'm running Rails 3.0.6, in case there's some functionality already built in with that.
我运行的是Rails 3.0.6，以防已经内置了一些功能。
Also, it's a Postgresql database, but ideally this would work across other databases.
此外，它是一个Postgresql数据库，但理想情况下，它可以跨其他数据库工作。
1 个解决方案
#1
3
A Pure SQL solution would be:
纯SQL解决方案是:
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).
您可以将DateDiff函数中的部分更改为所需的任何内容(小时、秒、毫秒等)。
#1
3
A Pure SQL solution would be:
纯SQL解决方案是:
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).
您可以将DateDiff函数中的部分更改为所需的任何内容(小时、秒、毫秒等)。