SQL 转换 HH:MM:SS
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16439558/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert HH:MM:SS
提问by GPH
I'm currently working on a call report however i've hit a snag. I need to calculate how long an agent has spent on the phone, the source data is in HH:MM:SS for the call duration. as in 1hr 12mins 10 seconds as appose to a number of seconds. So if 2 agents had taken 2 calls the time spent would sum up for that day.
我目前正在处理通话报告，但遇到了障碍。我需要计算座席在电话上花费了多长时间，源数据在呼叫持续时间的 HH:MM:SS 中。就像在 1 小时 12 分 10 秒内一样，大约是几秒。因此，如果 2 个座席接听了 2 个电话，则该天花费的时间将相加。
Is it possible to change this into seconds? or can anyone suggest something a but better?
是否可以将其更改为秒？或者有人可以提出更好的建议吗？
采纳答案by Artur Udod
If column type is datetimethen:
如果列类型是datetime：
(DATEPART(hh, @yourValue) * 60 * 60) + (DATEPART(mi, @yourValue) * 60) + DATEPART(s, @yourValue)
回答by Chris Moutray
Time to Seconds
时间到秒
Assuming it's a time datatype then you can change to seconds like this
假设它是时间数据类型，那么您可以像这样更改为秒
DATEDIFF(second, 0, @YourTimeValue)
And here's a simple aggregation example (ie sum)
这是一个简单的聚合示例（即总和）
DECLARE @data TABLE (TimeColumn TIME)
INSERT INTO @data values ('01:12:10'), ('02:15:45')
SELECT SUM(DATEDIFF(SECOND, 0, TimeColumn)) FROM @data
Which results in 12475 seconds
结果是 12475 秒
Seconds to Time
秒到时间
And I guess to complete the picture to convert back to time format from seconds
我想完成图片从秒转换回时间格式
SELECT CAST(DATEADD(SECOND, @TotalSecondsValue, 0) AS TIME)
or as part of the aggregation example
或作为聚合示例的一部分
DECLARE @data TABLE (TimeColumn TIME)
INSERT INTO @data VALUES ('01:12:10'), ('02:15:45')
SELECT CAST(DATEADD(SECOND, SUM(DATEDIFF(SECOND, 0, TimeColumn)), 0) AS TIME) FROM @data
Which results in a time of 03:27:55
结果是 03:27:55
