SQL 如何总结SQL Server中的时间字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9725732/
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
How to sum up time field in SQL Server
提问by Sas
I have a column called "WrkHrs" and the data type is time(hh:mm:ss). I want to sum up the working hours for employees. But since it's time data type sql server doesn't let me use like sum(columnname).
我有一个名为“WrkHrs”的列，数据类型是 time(hh:mm:ss)。我想总结一下员工的工作时间。但是因为是时候数据类型 sql server 不允许我使用 like sum(columnname).
How can I sum up the time data type fieled in sql query?
如何总结sql查询中的时间数据类型？
回答by Aaron Bertrand
SELECT EmployeeID, minutes_worked = SUM(DATEDIFF(MINUTE, '0:00:00', WrkHrs))
FROM dbo.table
-- WHERE ...
GROUP BY EmployeeID;
You can format it pretty on the front end. Or in T-SQL:
您可以在前端对其进行漂亮的格式化。或者在 T-SQL 中：
;WITH w(e, mw) AS
(
SELECT EmployeeID, SUM(DATEDIFF(MINUTE, '0:00:00', WrkHrs))
FROM dbo.table
-- WHERE ...
GROUP BY EmployeeID
)
SELECT EmployeeID = e,
WrkHrs = RTRIM(mw/60) + ':' + RIGHT('0' + RTRIM(mw%60),2)
FROM w;
However, you're using the wrong data type. TIMEis used to indicate a point in time, not an interval or duration. Wouldn't it make sense to store their work hours in two distinct columns, StartTimeand EndTime?
但是，您使用了错误的数据类型。TIME用于指示时间点，而不是间隔或持续时间。那岂不是有意义的存储他们的工作时间在两个不同的列，StartTime和EndTime？
回答by A Ghazal
In order to sum up the working hours for an employee you can calculate the difference between the shift start time and end time in minutes and convert it to readable format as following:
为了总结员工的工作时间，您可以计算班次开始时间和结束时间之间的差异（以分钟为单位），并将其转换为可读格式，如下所示：
DECLARE @StartTime datetime = '08:00'
DECLARE @EndTime datetime = '10:47'
DECLARE @durMinutes int
DECLARE @duration nvarchar(5)
SET @durMinutes = DATEDIFF(MINUTE, @StartTime, @EndTime)
SET @duration =
(SELECT RIGHT('00' + CAST((@durMinutes / 60) AS VARCHAR(2)),2) + ':' +
RIGHT('00' + CAST((@durMinutes % 60) AS VARCHAR(2)), 2))
SELECT @duration
The result : 02:47two hours and 47 minutes
其结果是：02:472小时47分钟
回答by Alex Petrov
For MS SQL Server, when your WorkingTime is stored as a time, or a varchar in order to sum it up you should consider that:
对于 MS SQL Server，当您的 WorkingTime 存储为时间或 varchar 以便总结时，您应该考虑：
1) Time format is not supporting sum, so you need to parse it
1）时间格式不支持sum，需要解析
2) 23:59:59.9999999 is the maximum value for the time.
2) 23:59:59.9999999 是时间的最大值。
So, the code that will work to get you the total number of WorkingHours:WorkingMinutes:WorkingSeconds would be the following:
因此，可以让您获得 WorkingHours:WorkingMinutes:WorkingSeconds 总数的代码如下：
SELECT
CAST(FORMAT((SUM((DATEPART("ss",WorkingTime) + DATEPART("mi",WorkingTime) * 60 + DATEPART("hh",WorkingTime) * 3600)) / 3600),'00') as varchar(max)) + ':' +
CAST(FORMAT((SUM((DATEPART("ss",WorkingTime) + DATEPART("mi",WorkingTime) * 60 + DATEPART("hh",WorkingTime) * 3600)) % 3600 / 60),'00') as varchar(max)) + ':' +
CAST(FORMAT((SUM((DATEPART("ss",WorkingTime) + DATEPART("mi",WorkingTime) * 60 + DATEPART("hh",WorkingTime) * 3600)) % 3600 % 60),'00') as varchar(max)) as WorkingTimeSum
FROM TableName
回答by Murugan
DECLARE @Tab TABLE
(
data CHAR(5)
)
INSERT @Tab
SELECT '25:30' UNION ALL
SELECT '31:45' UNION ALL
SELECT '16:00'
SELECT STUFF(CONVERT(CHAR(8), DATEADD(SECOND, theHours + theMinutes,
'19000101'), 8), 1, 2, CAST((theHours + theMinutes) / 3600 AS VARCHAR(12)))
FROM (
SELECT ABS(SUM(CASE CHARINDEX(':', data) WHEN 0 THEN 0 ELSE 3600 *
LEFT(data, CHARINDEX(':', data) - 1) END)) AS theHours,
ABS(SUM(CASE CHARINDEX(':', data) WHEN 0 THEN 0 ELSE 60 *
SUBSTRING(data, CHARINDEX(':', data) + 1, 2) END)) AS theMinutes
FROM @Tab
) AS d
