如何在SQL Server中对时间字段求和

Question

我有一个名为"WrkHrs"的列，数据类型为时间(hh:mm:ss)。 我想为员工总结工作小时数。 但是，由于它是时间数据类型， SQL Server 不允许我使用像 sum(columnname) 这样的语句。

在 SQL 查询中如何对时间数据类型字段进行求和？

- Sas

---

为什么你要使用 TIME 来处理这个，而不是使用 INT 或者 NUMERIC 呢？既然数据是 TIME 类型的，当你聚合的工作小时数大于 24 小时时，你的查询结果应该返回什么呢？结果应该是一个 INT 吗？ - Lamak

@Lamak 是的，我认为我可能也会使用数值数据类型。这对我来说会更容易。 - Sas

@AaronBertrand 这是一个列。 - Sas

我知道它是一个列。我在质疑它是否应该是这样。 - Aaron Bertrand

实际上，我有“开始”和“结束”两列。由于我对如何计算时间差感到困惑，所以我创建了另一列作为“小时数”，让用户手动输入。这样我就可以轻松地总结“小时数”列。但现在你给了我一个好的线索，我会尝试在代码中完成它。 - Sas

Answer 1

SELECT EmployeeID, minutes_worked = SUM(DATEDIFF(MINUTE, '0:00:00', WrkHrs)) 
FROM dbo.table 
-- WHERE ...
GROUP BY EmployeeID;

你可以在前端很好地格式化它。或者在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;

然而，您使用的数据类型不正确。TIME用于表示时间点，而不是间隔或持续时间。将他们的工作时间存储在两个不同的列中，StartTime和EndTime，这样做是否有意义呢？

- Aaron Bertrand

---

我尝试了两列，但问题在于timediff函数只能计算小时或分钟之间的差异，而不能同时计算。我找不到一种同时计算小时和分钟的方法，所以我选择了这种方式。 - Sas

在我看来，这不是一个问题。你总是可以从分钟中获取小时/分钟。230分钟=2小时50分钟。我在我的答案中确切地演示了这一点。 - Aaron Bertrand

Answer 2

select DATEDIFF(MINUTE, '0:00:00', '00:02:08')

结果为：2

select DATEDIFF(SECOND, '0:00:00', '00:02:08')

结果为：128

使用秒可以得到更好的答案。

因此，我认为答案可能是：

SELECT
    EmployeeId
    , seconds_worked = SUM (DATEDIFF (SECOND, '0:00:00', WrkHrs))
FROM
    tbl_employee
GROUP BY
    EmployeeId;

- ankur

---

这并没有解决问题中的求和部分。问题中只提到了一个时间列，但使用datediff涉及两个值。 - shawnt00

更新了答案，感谢指出。 - ankur

DATEDIFF期望的单位是SECOND，而不是SECONDS。 - smolo

Answer 3

为了计算员工的工作时间，您可以计算班次开始时间和结束时间之间的分钟差，并将其转换为可读格式，如下所示：

    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

结果为：02:47 两小时四十七分钟。

- A Ghazal

Answer 4

对于MS SQL Server，当你的WorkingTime以时间或varchar存储时，为了求和，你需要考虑以下几点：

1）时间格式不支持求和，因此你需要解析它

2）23:59:59.9999999是时间的最大值。

因此，下面的代码可以计算出工作时间的总小时数、分钟数和秒数：

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

- Alex Petrov

Answer 5

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

- Murugan

Answer 6

这应该很简单。

步骤

• 将时间转换为秒
• 求和结果
• 将总和转换为时间 例如： 假设您想对以下时间求和：

| present_hours   |
|-----------------|
| 00:01:20.000000 |
|-----------------|
| 00:01:13.000000 |
|-----------------|
| 00:01:45.000000 |
|-----------------|
| 00:01:03.000000 |
|-----------------|
| 00:01:10.000000 |
|-----------------|
| 00:00:56.000000 |

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(present_hours))) as total_present_hours FROM time_booking;

- sesu raj