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

时间:2022-04-26 22:24:47

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


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


1  

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


1  

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


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


1  

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


1  

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版本。