SQL Sever 2005 中的平均时间值

Question

我在 SQL Sever 2005 中有一个 varchar 字段，它以“hh:mm”ss.mmmm”格式存储时间值。

我真正想做的是使用这些时间值的内置聚合函数取平均值。然而，这

SELECT AVG(TimeField) FROM TableWithTimeValues

不起作用，因为（当然）SQL 不会平均 varchars 但是，

SELECT AVG(CAST(TimeField as datetime)) FROM TableWithTimeValues

据我所知，SQL 不知道如何仅用时间来转换值。并且没有将日期转换为日期时间字段，我已经尝试了多种方法来让 SQL 将该字段转换为日期时间，但到目前为止，没有

人能提出更好的方法吗？

Answer 1

SQL Server 可以将日期时间值的仅时间部分从字符串转换为日期时间，但是在您的示例中，您的精度为 4 位小数。 SQL Server 2005 只识别 3 个位置。 因此，您需要截断最右边的字符：

create table #TableWithTimeValues
(
    TimeField varchar(13) not null
)

insert into #TableWithTimeValues
select '04:00:00.0000'
union all
select '05:00:00.0000'
union all
select '06:00:00.0000'

SELECT CAST(TimeField as datetime) FROM #TableWithTimeValues
--Msg 241, Level 16, State 1, Line 1
--Conversion failed when converting datetime from character string.

SELECT CAST(LEFT(TimeField, 12) as datetime) FROM #TableWithTimeValues
--Success!

这会将有效值转换为从 1900-01-01 开始的 DATETIME。 SQL Server 根据 1 天 = 1（整数）计算日期。 天的部分是值 1 的部分（即中午是 0.5）。 由于转换中未指定日期，因此 SQL Server 指定了 0 天 (1900-01-01) 值，这满足了我们对时间部分进行平均的需要。

要对 DATETIME 执行 AVG 运算，必须首先将 DATETIME 转换为十进制值，执行聚合，然后转换回来。 例如，

SELECT CAST(AVG(CAST(CAST(LEFT(TimeField, 12) as datetime) AS FLOAT)) AS DATETIME) FROM #TableWithTimeValues
--1900-01-01 05:00:00.000

如果您需要使用额外的小数位来存储它，则可以将 DATETIME 转换为仅包含时间部分的 VARCHAR，并将字符串填充回 13 个字符：

SELECT CONVERT(VARCHAR, CAST(AVG(CAST(CAST(LEFT(TimeField, 12) as datetime) AS FLOAT)) AS DATETIME), 114) + '0' FROM #TableWithTimeValues

Answer 2

试试这个

AVG(CAST(CAST('1900-01-01 ' + TimeField AS DateTime) AS Float))

无论如何你真的应该将它们存储在日期时间列中。 只需为该部分使用一致的日期（1/1/1900 很常见）。 然后你可以只调用 AVG() 而不用担心它。

Answer 3

我使用 Cadaeic 的回复来获得我正在寻找的答案，所以我认为我应该分享代码......

我正在寻找一个查询，该查询可以平均我所有的时间，并为我提供所有批准的总体周转时间。 下面是一个嵌套语句，它为您提供各个 id 的 AVG TAT，以及嵌套时的总体 TAT

SELECT 
-- calculates overall TAT for ALL Approvals for specified period of time
-- depending on parameters of query
CONVERT(VARCHAR, CAST(AVG(CAST(CAST(LEFT(Tat_mins, 12) as datetime) AS FLOAT)) AS DATETIME), 108) + '0'

from 
(
-- tat is for individual approvals 
SELECT 
dbo.credit_application.decision_status,
dbo.credit_application.application_id,
cast(dbo.credit_application.data_entry_complete as date) as'Data Entry Date',
cast(dbo.credit_application.decision_date as DATE) as 'Decision Date',
avg(datediff(minute, dbo.credit_application.data_entry_complete, dbo.credit_application.decision_date)) as 'TAT Minutes',
convert (char(5), DateAdd(minute, Datediff(minute,dbo.credit_application.data_entry_complete, dbo.credit_application.decision_date),'00:00:00'),108) as 'TAT_Mins'
FROM  dbo.credit_application
where Decision_status not in ('P','N')

group by dbo.credit_application.decision_status,
dbo.credit_application.data_entry_complete,
dbo.credit_application.decision_date
--dbo.credit_application.application_id
)bb

Answer 4

您认为如何计算约会时间的平均值？

我猜你需要按某个时间段（小时？）进行分组，并显示计数（*）？

Answer 5

SQL Server 将日期时间数据存储为 2 个 4 字节整数，因此日期时间占用 8 个字节。 第一个是自基准日期以来的天数，第二个是自午夜以来的毫秒数。

您可以将日期时间值转换为整数并执行数学运算，但转换仅返回日期时间值的“天”部分，例如选择convert(int,getdate())。 将“时间”部分作为整数返回更加困难。

使用 SQL Server 2008 适合您吗？ 该版本具有新的专用时间数据类型。

谢谢，安迪。

Answer 6

我会计算出所有日期和任意点 (01/01/1900) 之间的差异，对其进行平均，然后将其添加回任意点。