SQL HH:MM:SS 格式的 DATEDIFF
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14445600/
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
DATEDIFF in HH:MM:SS format
提问by user1671731
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.
给定一些包含开始时间和结束时间的数据，我需要根据小时、分钟、秒和平均长度计算总长度。
For example the result must be something like 45:15:10which means 45 hours 15 min 10 sec, or 30:07for 30 min 07 sec.
例如，结果必须类似于45:15:10这意味着 45 小时 15 分 10 秒，或30:0730 分 07 秒。
We're using SQL Server 2008 R2and the conversion failed when time is more than 24:59:59. Any idea of how I could do this?
我们正在使用SQL Server 2008 R2并且当时间超过 时转换失败24:59:59。知道我怎么能做到这一点吗？
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.
对于信息，表中的列是Id、StartDateTime、EndDateTime等。我需要制作一份月度报告，其中包含该月的记录数、这些记录的总长度和平均长度。我想知道是否有一种简单的方法来执行所有这些操作。
采纳答案by Aaron Bertrand
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).
您不应该转换为time- 它旨在在单个 24 小时时钟上存储时间点，而不是持续时间或间隔（即使是将其自身限制为 < 24 小时，这显然不是您的数据）。相反，您可以在所需的最小间隔（在您的情况下，秒）中获取 datediff，然后执行一些数学和字符串操作以将其呈现为您需要的输出格式（也可能最好将秒数返回给应用程序或报告工具并让它完成这项工作）。
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;
Results:
结果：
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
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 CASEexpression:
这并不是您所要求的，因为它不会仅显示 MM:SS 小于 1 小时的增量。你可以用一个简单的CASE表达式来调整它：
;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;
This query changes the delta column in the 2nd row in the above result from 0:22:00to 22:00.
此查询将上述结果中第 2 行的 delta 列从0:22:00更改为22:00。
回答by michal.jakubeczy
I slightly modified Avinash's answer as it may end with error if difference is too big. If you need only HH:mm:ss it is sufficient to distinguish at seconds level ony like this:
我稍微修改了 Avinash 的答案，因为如果差异太大，它可能会以错误结束。如果您只需要 HH:mm:ss 就足以在秒级区分任何这样的：
SELECT CONVERT(time,
DATEADD(s,
DATEDIFF(s,
'2018-01-07 09:53:00',
'2018-01-07 11:53:01'),
CAST('1900-01-01 00:00:00.0000000' as datetime2)
)
)
回答by Avinash Reddy
SELECT CONVERT(time,
DATEADD(mcs,
DATEDIFF(mcs,
'2007-05-07 09:53:00.0273335',
'2007-05-07 09:53:01.0376635'),
CAST('1900-01-01 00:00:00.0000000' as datetime2)
)
)
回答by Bruno Leit?o
Starting in SQL SERVER 2012, you don't need to use DATEDIFF function. You can use FORMAT function to achieve what you want:
从 SQL SERVER 2012 开始，您不需要使用 DATEDIFF 函数。您可以使用 FORMAT 函数来实现您想要的：
SELECT
FORMAT(CONVERT(TIME, [appoitment].[Start] - [appointment].[End]), N'hh\:mm') AS 'Duration'
FROM
[tblAppointment] (NOLOCK)
回答by Gordon Linoff
If you want to do averages, then the best approach is to convert to seconds or fractions of a day. Day fractions are convenient in SQL Server, because you can do things like:
如果你想做平均值，那么最好的方法是转换为秒或一天的几分之一。天分数在 SQL Server 中很方便，因为您可以执行以下操作：
select avg(cast(endtime - starttime) as float)
from t
You can convert it back to a datetimeusing the reverse cast:
您可以datetime使用反向转换将其转换回 a ：
select cast(avg(cast(endtime - starttime as float) as datetime)
from t
The arithmetic to get the times in the format you want . . . that is a pain. You might consider including days in the final format, and using:
以您想要的格式获取时间的算法。. . 那是一种痛苦。您可以考虑在最终格式中包含天数，并使用：
select right(convert(varchar(255), <val>, 120), 10)
To get the hours exceeding 24, here is another approach:
要获得超过 24 小时的时间，这是另一种方法：
select cast(floor(cast(<val> as float)*24) as varchar(255))+right(convert(varchar(255), <val>, 120), 6)
It uses convertfor minutes and seconds, which should be padded with 0s on the left. It then appends the hours as a separate value.
它convert用于分钟和秒，应该在左边用 0 填充。然后将小时作为单独的值附加。
回答by Eugenio Andruskiewitsch
A way that avoids overflows and can include days and go all the way to milliseconds in the output:
一种避免溢出的方法，可以在输出中包括天数和毫秒数：
DECLARE @startDate AS DATETIME = '2018-06-01 14:20:02.100'
DECLARE @endDate AS DATETIME = '2018-06-02 15:23:09.000'
SELECT CAST(DATEDIFF(day,'1900-01-01', @endDate - @startDate) AS VARCHAR) + 'd ' + CONVERT(varchar(22), @endDate - @startDate, 114)
The above will return
以上将返回
1d 01:03:06:900
第一天 01:03:06:900
And, off course, you can use the formatting of your choice
而且，当然，您可以使用您选择的格式
SQL Supports datetime substraction which outputs a new datetime relative to the MIN date (for instance 1900-01-01, you can probably get this value from some system variable) This works better than DATEDIFF, because DATEDIFF will count ONE for each "datepart boundaries crossed", even if the elapsed time is less than a whole datapart. Another nice thing about this method is that it allows you to use the date formatting conversions.
SQL 支持日期时间减法，它输出相对于 MIN 日期的新日期时间（例如 1900-01-01，您可能可以从某个系统变量中获取此值）这比 DATEDIFF 效果更好，因为 DATEDIFF 将为每个“日期部分边界计数 1”交叉”，即使经过的时间少于整个数据部分。这种方法的另一个好处是它允许您使用日期格式转换。
