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小提琴
#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小提琴
#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版本。