代码之家 › 专栏 › 技术社区 › Nishan

从DateTime2列计算平均时间

sql-server sql

Nishan · 技术社区 · 7 年前

我有两个列,分别是employee in time和out time,但都在DateTime2中。

+-----------------------------+-----------------------------+-----------------------------+
|            Date             |           InTime            |           OutTime           |
+-----------------------------+-----------------------------+-----------------------------+
| 2019-01-01 00:00:00.0000000 | 2019-01-01 09:45:00.0000000 | 2019-01-01 11:14:00.0000000 | <
| 2019-01-02 00:00:00.0000000 | 1901-01-01 00:00:00.0000000 | 1901-01-01 00:00:00.0000000 |
| 2019-01-03 00:00:00.0000000 | 1901-01-01 00:00:00.0000000 | 1901-01-01 00:00:00.0000000 |
| 2019-01-04 00:00:00.0000000 | 2019-01-04 18:32:00.0000000 | 1901-01-01 00:00:00.0000000 | <
| 2019-01-05 00:00:00.0000000 | 1901-01-01 00:00:00.0000000 | 1901-01-01 00:00:00.0000000 |
| 2019-01-06 00:00:00.0000000 | 1901-01-01 00:00:00.0000000 | 1901-01-01 00:00:00.0000000 |
| 2019-01-07 00:00:00.0000000 | 2019-01-07 14:17:00.0000000 | 2019-01-07 15:03:00.0000000 | <
| 2019-01-08 00:00:00.0000000 | 1901-01-01 00:00:00.0000000 | 1901-01-01 00:00:00.0000000 |
+-----------------------------+-----------------------------+-----------------------------+

如您所见,有些行包含时间,但有些行不包含时间。我想删除日期部分,获得两列的时间平均值。

到目前为止,我一直在使用这个查询。

SELECT  CONVERT(varchar(5), 
            Cast(DateAdd(ms, AVG(CAST(DateDiff( ms, '00:00:00', cast(InTime as time)) AS bigint)), '00:00:00' ) as Time ),108) AS 'AVG_IN_TIME',
        CONVERT(varchar(5), 
            Cast(DateAdd(ms, AVG(CAST(DateDiff( ms, '00:00:00', cast(OutTime as time)) AS bigint)), '00:00:00' ) as Time ),108) AS 'AVG_OUT_TIME'
FROM [HCMSync].[dbo].[Attendances]
Where [Email address] = 'email@domain.com' and [Date] between '2019-01-01' and '2019-01-08'

但这给了我,

+-------------+--------------+
| AVG_IN_TIME | AVG_OUT_TIME |
+-------------+--------------+
| 05:19       | 03:17        |
+-------------+--------------+

我不认为这是准确的,因为如果只有一个记录,像这样,

+-----------------------------+-----------------------------+
|           InTime            |           OutTime           |
+-----------------------------+-----------------------------+
| 1901-01-01 00:00:00.0000000 | 1901-01-01 00:00:00.0000000 |
| 1901-01-01 00:00:00.0000000 | 1901-01-01 00:00:00.0000000 |
| 1901-01-01 00:00:00.0000000 | 1901-01-01 00:00:00.0000000 |
| 1901-01-01 00:00:00.0000000 | 1901-01-01 00:00:00.0000000 |
| 1901-01-01 00:00:00.0000000 | 1901-01-01 00:00:00.0000000 |
| 1901-01-01 00:00:00.0000000 | 1901-01-01 00:00:00.0000000 |
| 2019-01-07 09:42:00.0000000 | 2019-01-07 11:23:00.0000000 | < This record.
| 1901-01-01 00:00:00.0000000 | 1901-01-01 00:00:00.0000000 |
+-----------------------------+-----------------------------+

..它返回以下结果。它应该给我提供09:42和11:23。我甚至手动计算了平均值,结果却不一样。

+-------------+--------------+
| AVG_IN_TIME | AVG_OUT_TIME |
+-------------+--------------+
| 01:12       | 01:25        |
+-------------+--------------+

- 操作员,并导致,

操作数数据类型datetime2对于减法运算符无效。

一些答案试图将datetime字段转换为float,结果,

不允许将数据类型datetime2显式转换为float。

还有别的办法吗?

2 回复 | 直到 7 年前

dnoeth 7 年前

那些午夜的争吵导致了这个问题,他们降低了平均值。

必须使用NULLIF将其从平均计算中删除。

SELECT  CONVERT(varchar(5), 
            Cast(DateAdd(s, AVG(NULLIF(DateDiff( s, '00:00:00', cast(InTime as time)), 0)), '00:00:00' ) as Time ),108) AS 'AVG_IN_TIME',
        CONVERT(varchar(5), 
            Cast(DateAdd(s, AVG(NULLIF(DateDiff( s, '00:00:00', cast(OutTime as time)),0)), '00:00:00' ) as Time ),108) AS 'AVG_OUT_TIME'

我删除了bigint cast并切换到seconds,因为你的结果只需要几分钟。

Yann G 7 年前

这里有一个实现这一点的方法

    SELECT (SUM(DATEPART(HOUR, InTime) * 60) + SUM(DATEPART(MINUTE, InTime))) / (SELECT COUNT(InTime) FROM #Temp WHERE DATEPART(HOUR, InTime) <> 0) / 60 AS [InTime avg hours]
        , (SUM(DATEPART(HOUR, InTime) * 60) + SUM(DATEPART(MINUTE, InTime))) / (SELECT COUNT(InTime) FROM #Temp WHERE DATEPART(HOUR, InTime) <> 0) % 60 AS [InTime avg minutes]
        , (SUM(DATEPART(HOUR, OutTime) * 60) + SUM(DATEPART(MINUTE, OutTime))) / (SELECT COUNT(OutTime) FROM #Temp WHERE DATEPART(HOUR, OutTime) <> 0) / 60 AS [OutTime avg hours]
        , (SUM(DATEPART(HOUR, OutTime) * 60) + SUM(DATEPART(MINUTE, OutTime))) / (SELECT COUNT(OutTime) FROM #Temp WHERE DATEPART(HOUR, OutTime) <> 0) % 60 AS [OutTime avg minutes]
    FROM #Temp