我需要计算总长度的小时，分钟，秒，和平均长度，给定一些数据的开始时间和结束时间。
例如，结果必须是45:15:10，表示45小时15分10秒，或30:07表示30分07秒。
我们使用SQL Server 2008 R2，当时间超过24:59:59时，转换失败。知道我该怎么做吗?
对于信息，表中的列为Id、StartDateTime、EndDateTime等。我需要做一份月度报告，包括当月的记录计数，记录的总长度和平均长度。我想知道是否有一个简单的方法来执行所有这些
您不应该转换为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;
结果:
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 delta <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;
此查询将上述结果中第二行的增量列从0:22:00更改为22:00。
我稍微修改了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)
)
)
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)
)
)
如果您想计算平均值，那么最好的方法是将其转换为秒或一天的一小部分。在SQL Server中，日分数很方便，因为您可以执行以下操作:
select avg(cast(endtime - starttime) as float)
from t
您可以使用反强制转换将其转换回datetime:
select cast(avg(cast(endtime - starttime as float) as datetime)
from t
以您想要的格式获得时间的算法…这是一种痛苦。您可以考虑在最终格式中包含天数，并使用:
select right(convert(varchar(255), <val>, 120), 10)
要获得超过24小时的时间，这里有另一种方法:
select cast(floor(cast(<val> as float)*24) as varchar(255))+right(convert(varchar(255), <val>, 120), 6)
它使用convert表示分和秒，应该在左边用0填充。然后将小时数作为一个单独的值追加。
从SQL SERVER 2012开始，不再需要使用DATEDIFF函数。你可以使用FORMAT函数来实现你想要的:
SELECT
FORMAT(CONVERT(TIME, [appoitment].[Start] - [appointment].[End]), N'hh:mm') AS 'Duration'
FROM
[tblAppointment] (NOLOCK)
一种避免溢出的方法，可以在输出中包含天数，并一直到毫秒:
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)
以上将返回
1 d 01:03:06:900
当然，你也可以使用自己选择的格式
SQL支持datetime减法，它输出相对于最小日期的新日期时间(例如1900-01-01，您可能可以从某些系统变量中获得此值)。这比DATEDIFF工作得更好，因为DATEDIFF将为每个"跨越的日期部分边界"计数1，即使经过的时间小于整个数据间隔。这个方法的另一个优点是它允许您使用日期格式转换。
如果days为(正)天数，如0.5为12小时，则使用此表达式将其格式化为适当的持续时间:
CONVERT(varchar(9), FLOOR(days * 24)) + RIGHT(CONVERT(char(19), CAST(days AS datetime), 120), 6)
粘贴时，Excel将理解9999:59:59以内的值。在英文版本中应用自定义格式:[h]:mm:ss(荷兰语版本为[u]:mm:ss)。