如何获得dateiff的平均时间？

With the below query I'm getting the DATEDIFF of each record in the table.

通过以下查询,我得到表中每条记录的DATEDIFF。

How can I get it to get the AVG of the datediff of all records?

我如何才能获得所有记录的日期安全的AVG?

I don't care about the date only the time.

我不关心日期的时间。

SELECT 
right('0' + convert(float,datediff 
                    (second, callstartdt,endstartdt)/ 60 % 60), 2) + ':' +
right('0' + convert(float,datediff 
                    (second, callstartdt,endstartdt)% 60),2)  as total_time
FROM times

http://sqlfiddle.com/#!6/f01c6/2

I have sql 2012.

我有sql 2012。

2 个解决方案

#1

You can just plop an AVG() in there:

你可以在那里放一个AVG():

SELECT 
    right('0' + convert(float,AVG(datediff (second, callstartdt,endstartdt))/ 60 % 60), 2) + ':' 
  + right('0' + convert(float,AVG(datediff (second, callstartdt,endstartdt))% 60),2)  as total_time
FROM times

Demo: SQL Fiddle

演示:SQL小提琴

#2

with avg_s as (
    select avg(datediff(ss, callstartdt, endstartdt)) as ss from times
)
select
    ss / 86400 as days,
    ss % 86400 / 3600 as hours,
    ss % 3600 / 60 as minutes,
    ss % 60 as seconds,
    concat(
        format(ss / 3600, '00:'), /* could be greater than 24 */
        format(ss % 3600 / 60, '00:'),
        format(ss % 60, '00')
    )
from avg_s

The older hack with datetime would have you doing something like this but a lot of it is deprecated in later versions of Sql Server.

使用datetime的旧hack会让你做这样的事情,但是在Sql Server的更高版本中不推荐使用它。

select cast(avg(
        cast(cast(endstartdt as datetime) -
        cast(callstartdt as datetime)
    as float)) as datetime)
from times

#1