sql server-sql Datediff，以秒为单位，带小数点

Question

我正在尝试提取两个SQL DateTime值之间的差异(以秒为单位(，并使用小数位进行一些性能监视。

我有一个表，"Pagelog"，它有一个"创建"one_answers"结束"日期时间。在过去，我已经能够做到以下几点：

SELECT DATEDIFF(ms, pagelog_created, pagelog_end)/1000.00 as pl_duration FROM pagelog

然而，我已经开始得到以下错误：

Msg 535, Level 16, State 0, Line 1
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

我看到过很多人对这个错误的回应，说我应该使用一个不太精确的测量单位。但当我需要区分2.1秒和2.9秒时，这几乎没有帮助，因为DATEDIFF(s，..，..(将返回INT结果，并失去我需要的准确性。

我最初认为这是由我的表中的几个值引起的，这些值有很大的范围，但运行这个：

SELECT DATEDIFF(s, pagelog_created, pagelog_end) FROM pagelog
ORDER BY DATEDIFF(s, pagelog_created, pagelog_end) DESC

返回30837的最大值，即8.5小时或30837000毫秒，据我所知，在SQL INT的范围内？

任何帮助都将不胜感激，据我所知，我有两个选择：

• 以某种方式解决数据问题，找到罪魁祸首值
• 找到一种不同的方法来计算值之间的差异

谢谢！

Answer 1

StackOverflow魔术似乎奏效了，尽管上周花了几个小时在这个问题上，但我重读了我的问题，现在已经解决了这个问题。我想我会更新答案来帮助其他有这个问题的人。

这里的问题不是有一个大范围，而是有一个负的范围。这显然会导致负溢出。如果SQL Server错误更具描述性，但在技术上没有错，那会很有帮助。

所以在我的情况下，这是返回值：

SELECT * FROM pagelog
WHERE pagelog_created > pagelog_end

要么删除这些值，要么从初始结果集中省略它们！

感谢Ivan G和Andriy M的回复。

Answer 2

您可以尝试避免这样的溢出：

DECLARE @dt1 DATETIME = '2013-01-01 00:00:00.000'
DECLARE @dt2 DATETIME = '2013-06-01 23:59:59.997'
SELECT  DATEDIFF(DAY, CAST(@dt1 AS DATE), CAST(@dt2 AS DATE)) * 24 * 60 * 60
SELECT  DATEDIFF(ms, CAST(@dt1 AS TIME), CAST(@dt2 AS TIME))/1000.0
SELECT DATEDIFF(DAY, CAST(@dt1 AS DATE), CAST(@dt2 AS DATE)) * 24 * 60 * 60 
        + DATEDIFF(ms, CAST(@dt1 AS TIME), CAST(@dt2 AS TIME))/1000.0 

首先，它从DATETIME的DATE部分获得全天的秒数，然后从TIME部分添加秒数，之后，它只添加它们。

不会出现错误，因为TIME数据类型中最小和最大时间的DATEDIFF无法产生溢出。

Answer 3

您当然可以这样做：

SELECT
  DATEDIFF(ms, DATEADD(s, x.sec, pagelog_created), pagelog_end) * 0.001
  + x.sec AS pl_duration
FROM pagelog
CROSS APPLY (
  SELECT DATEDIFF(s, pagelog_created, pagelog_end)
) x (sec)
;

正如您所看到的，首先，取pagelog_created和pagelog_end之间的秒差，然后将秒加回到pagelog_created，并计算该值和pagelog_end之间的毫秒差并将其添加到秒中。

然而，根据您的调查，由于表中似乎没有可能导致溢出的行，我还将仔细检查特定片段是否是错误的来源。

Answer 4

with cte as(
select rownum = row_number() over(partition by T.TR_ID order by T.[date]),
T.* from [dbo].[TR_Events] T 
)
select  cte.[date],nex.[date],convert(varchar(10),datediff(s, cte.[date], nex.[date])/3600)+':'+
convert(varchar(10),datediff(s, cte.[date], nex.[date])%3600/60)+':'+
convert(varchar(10),(datediff(s,cte.[date], nex.[date])%60)) 
from cte
left join cte prev on prev.rownum = cte.rownum - 1
left join cte nex on nex.rownum = cte.rownum + 1