DATEDIFF在HH：MM：SS格式 [英] DATEDIFF in HH:MM:SS format

问题描述

我需要以小时，分，秒和平均长度计算总长度，给出一些开始时间和结束时间的数据。

I need to calculate the total length in terms of Hours, Minutes, Seconds, and the average length, given some data with start time and end time.

例如，结果必须像 45:15:10 这意味着45小时15分10秒，或 30:07 30分钟07秒

For example the result must be something like 45:15:10 which means 45 hours 15 min 10 sec, or 30:07 for 30 min 07 sec.

我们使用 SQL Server 2008 R2 ，当时间是超过 24:59:59 。有什么想法可以这样做？

We're using SQL Server 2008 R2 and the conversion failed when time is more than 24:59:59. Any idea of how I could do this?

有关信息，表中的列是 Id ， StartDateTime ， EndDateTime 等。我需要每月发送一次报告，其中包含月份的记录数，这些记录和平均长度。我想知道是否有一个简单的方法来执行所有这些。

For information, the columns in the table are Id, StartDateTime, EndDateTime, etc. I need to make a monthly report which contains the recordings count of the month, the total length of these records, and the average length. I'd like to know if there is an easy way to perform all of this.

推荐答案

你不应该转换为 time - 它意味着在一个24小时时钟上存储一个时间点，而不是持续时间或间隔（即使是自己限制在<这显然是你的数据没有）。相反，您可以以所需的最小间隔（在您的情况下为秒）使用datediff，然后执行一些数学和字符串操作，以您需要的输出格式显示它（也许最好将秒返回到应用程序或报告工具，并让它做这个工作）。

You shouldn't be converting to time - it is meant to store a point in time on a single 24h clock, not a duration or interval (even one that is constrained on its own to < 24 hours, which clearly your data is not). Instead you can take the datediff in the smallest interval required (in your case, seconds), and then perform some math and string manipulation to present it in the output format you need (it might also be preferable to return the seconds to the application or report tool and have it do this work).

DECLARE @d TABLE
(
  id INT IDENTITY(1,1), 
  StartDateTime DATETIME, 
  EndDateTime DATETIME
);

INSERT @d(StartDateTime, EndDateTime) VALUES 
(DATEADD(DAY, -2, GETDATE()), DATEADD(MINUTE, 15, GETDATE())),
(GETDATE()                  , DATEADD(MINUTE, 22, GETDATE())),
(DATEADD(DAY, -1, GETDATE()), DATEADD(MINUTE,  5, GETDATE())),
(DATEADD(DAY, -4, GETDATE()), DATEADD(SECOND, 14, GETDATE()));

;WITH x AS (SELECT id, StartDateTime, EndDateTime, 
  d = DATEDIFF(SECOND, StartDateTime, EndDateTime),
  a = AVG(DATEDIFF(SECOND, StartDateTime, EndDateTime)) OVER()
  FROM @d
)
SELECT id, StartDateTime, EndDateTime,
  [delta_HH:MM:SS] = CONVERT(VARCHAR(5), d/60/60)
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2), d/60%60), 2)
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2), d % 60), 2),
  [avg_HH:MM:SS] = CONVERT(VARCHAR(5), a/60/60)
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2), a/60%60), 2)
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2), a % 60), 2)
FROM x;

结果：

id  StartDateTime        EndDateTime          delta_HH:MM:SS  avg_HH:MM:SS
--  -------------------  -------------------  --------------  ------------
1   2013-01-19 14:24:46  2013-01-21 14:39:46  48:15:00        42:10:33
2   2013-01-21 14:24:46  2013-01-21 14:46:46   0:22:00        42:10:33
3   2013-01-20 14:24:46  2013-01-21 14:29:46  24:05:00        42:10:33
4   2013-01-17 14:24:46  2013-01-21 14:25:00  96:00:14        42:10:33

这不是你所要求的，因为它不会只显示MM：SS for deltas< 1小时。您可以使用简单的 CASE 表达式进行调整：

This isn't precisely what you asked for, as it won't show just MM:SS for deltas < 1 hour. You can adjust that with a simple CASE expression:

;WITH x AS (SELECT id, StartDateTime, EndDateTime, 
  d = DATEDIFF(SECOND, StartDateTime, EndDateTime),
  a = AVG(DATEDIFF(SECOND, StartDateTime, EndDateTime)) OVER()
  FROM @d
)
SELECT id, StartDateTime, EndDateTime,
  [delta_HH:MM:SS] = CASE WHEN d >= 3600 THEN 
    CONVERT(VARCHAR(5), d/60/60) + ':' ELSE '' END
  + RIGHT('0' + CONVERT(VARCHAR(2), d/60%60), 2)
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2), d % 60), 2),
  [avg_HH:MM:SS] = CASE WHEN a >= 3600 THEN 
    CONVERT(VARCHAR(5), a/60/60) + ':' ELSE '' END
  + RIGHT('0' + CONVERT(VARCHAR(2), a/60%60), 2)
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2), a % 60), 2)
FROM x;

此查询将上述结果中的第二行中的delta列更改为 0:22:00 至 22:00 。

This query changes the delta column in the 2nd row in the above result from 0:22:00 to 22:00.

这篇关于DATEDIFF在HH：MM：SS格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！