如何对 hh:mm:ss 中的 Time 数据类型列的时间求和? [英] How to sum times from Time datatype columns in hh:mm:ss?

问题描述

我有一个包含 4 列的 SQL Server 表(TIME(0) 数据类型).

I have a SQL Server table with 4 columns (TIME(0) datatype).

每一行对应一天，所以我有:

Each row correspond to a day so I have:

Col Time 1 -------- Col Time 2 ------ Col Time 3

'03:23:12' -------- '21:33:04' ------ '07:44:11'
'21:14:45' -------- '12:43:44' ------ '01:56:43'
'15:22:36' -------- '19:33:55' ------ '04:03:11'
'08:21:46' -------- '01:23:14' ------ '06:09:11'

我想对每一列进行SUM，以便获得总小时数、分钟数和秒数 (hh:mm:ss).

I want to SUM each column so I can get the total hours, minutes and seconds (hh:mm:ss).

例如对于 Col Time 1 我应该有:

So for example for Col Time 1 I should have:

48:54:46

推荐答案

开始，你的最终值应该是 48:22:19 而不是 48:54:46.
其次，你不能用TIME类型来接收你想要的结果，因为hh的元素范围是两位数，范围从 0 到 23，代表小时(更多信息 ).

At first, your final value should be 48:22:19 instead of 48:54:46.
Secondly, you can't use TIME type to receive the result you want, because element range for hh is two digits, ranging from 0 to 23, that represent the hour( More information ).

这里有一个解决方法，当首先你 SUM TIME 列在 秒 内转换每个值，然后分别查找 小时，分和秒 :

Here comes a workaround, when at first you SUM TIME column converting each value in seconds and then find separately hours, minutes and seconds :

SELECT CAST(t.time_sum/3600 AS VARCHAR(2)) + ':'
     + CAST(t.time_sum%3600/60 AS VARCHAR(2)) + ':'
     + CAST(((t.time_sum%3600)%60) AS VARCHAR(2))
FROM ( SELECT SUM(DATEDIFF(S, '00:00:00', col1)) AS time_sum
       FROM tbl) t

SQLFiddle

这篇关于如何对 hh:mm:ss 中的 Time 数据类型列的时间求和?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！