DATEDIFF函数导致SQL Server 2016中出现溢出

I have a table in SQL Server 2016 with the following data:

我在SQL Server 2016中有一个表，其中包含以下数据：

id     | t                       | memory
-------+-------------------------+------------
620255 | 2017-07-17 16:11:25.100 | 11632640
620127 | 2017-07-17 16:11:24.100 | 11632640
619999 | 2017-07-17 16:11:23.097 | 11632640
619872 | 2017-07-17 16:11:22.097 | 11632640

I am trying to get the average of the memory per second. So in order to do that I tried the below query:

我试图获得每秒的平均内存。所以为了做到这一点，我尝试了以下查询：

SELECT 
    AVG(memory) AS avgMemory, 
    DATEADD(second, DATEDIFF(second, 0, t), 0) AS t 
FROM 
    Table1 AS Table1 
WHERE 
    t BETWEEN '2017-07-17 16:11:00.000' AND '2017-07-17 16:12:00.000' 
GROUP BY 
    DATEADD(second, DATEDIFF(second, 0, t), 0) 
ORDER BY 
    t ASC;

But upon execution, I get the following error:

但执行时，我收到以下错误：

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.

日期函数导致溢出。分隔两个日期/时间实例的日期部分数量太大。尝试使用具有不太精确的日期部分的datediff。

As per the official docs and this SO post, the maximum difference for seconds can be 68 years, but in my case it is just 60 seconds.

根据官方文档和SO帖子，秒的最大差异可能是68年，但在我的情况下它只有60秒。

So why am I getting this error? And what can I do to resolve it?

那么为什么我会收到这个错误？我该怎么做才能解决它？

Here is the SQLFiddle with the mentioned issue: Fiddle

这是带有上述问题的SQLFiddle：小提琴

3 个解决方案

#1

The problem here is the "starting date".

这里的问题是“开始日期”。

In select DATEDIFF(second, 0, t) 0 means 1900-01-01 that is too distant from 2017, so the time in seconds passed from 1900-01-01 to 2017-07-17 overflows simple integer, it's not just "60 seconds"

在选择DATEDIFF（秒，0，t）中0表示距离2017年太远的1900-01-01，因此从1900-01-01到2017-07-17的时间以秒为单位溢出简单整数，它不仅仅是“ 60秒“

#2

You can also use a different date (e.g. 2017-07-17 00:00:00.000) as the fixpoint for your calculations:

您还可以使用其他日期（例如2017-07-17 00：00：00.000）作为计算的固定点：

SELECT AVG(memory) AS memory, 
  DATEADD(second, 
          DATEDIFF(second, '2017-07-17 00:00:00.000', t),
          '2017-07-17 00:00:00.000') AS t 
FROM Table1 AS Table1 
WHERE t BETWEEN '2017-07-17 16:11:00.000' AND '2017-07-17 16:12:00.000' 
GROUP BY DATEADD(second, 
                 DATEDIFF(second, '2017-07-17 00:00:00.000', t), 
                 '2017-07-17 00:00:00.000') 
ORDER BY t ASC;

SQL Fiddle

SQL小提琴

#3

SQL Server offers datediff_big() for this purpose:

SQL Server为此提供了datediff_big（）：

SELECT AVG(memory) AS avgMemory,
       DATEADD(second, DATEDIFF_BIG(second, 0, t), 0) AS t 
FROM Table1 AS Table1 
WHERE t BETWEEN '2017-07-17 16:11:00.000' AND '2017-07-17 16:12:00.000' 
GROUP BY DATEADD(second, DATEDIFF_BIG(second, 0, t), 0) 
ORDER BY t ASC;

You could also do this by converting the value to a string:

您也可以通过将值转换为字符串来执行此操作：

SELECT AVG(memory) AS avgMemory,
       CONVERT(VARCHAR(19), t, 120) as t
FROM Table1 AS Table1 
WHERE t BETWEEN '2017-07-17 16:11:00.000' AND '2017-07-17 16:12:00.000' 
GROUP BY CONVERT(VARCHAR(19), t, 120)
ORDER BY t ASC;

This version works in all supported versions of SQL Server.

此版本适用于所有受支持的SQL Server版本。

#1