T-SQL按秒分组数据时遭遇算术溢出转换错误求助

问题背景 #

原本按分钟分组统计会话数的查询正常运行，但改为按秒分组后，即使使用DATEDIFF_BIG避免秒数溢出，仍出现错误：

Arithmetic overflow error converting expression to data type int.

解决方案 #

根据你的SQL Server版本，选择以下对应方案：

方案1：SQL Server 2016及更高版本

问题根源是@Value的INT类型导致运算结果被隐式转换回INT，只需将@Value强制转为BIGINT，确保全程用大整数运算：

-- 直接在运算中转换@Value类型
SELECT TOP 200   
    DATEADD(SECOND, 
            (DATEDIFF_BIG(SECOND, 0, TIMESTAMP) / CAST(@Value AS BIGINT)) * CAST(@Value AS BIGINT), 
            0) AS [TIMESTAMP],
    COUNT(Something) AS [# of Sessions] 
FROM
    table WITH (NOLOCK)
WHERE
    TIMESTAMP BETWEEN @Start AND @End  
GROUP BY
    DATEADD(SECOND, 
            (DATEDIFF_BIG(SECOND, 0, TIMESTAMP) / CAST(@Value AS BIGINT)) * CAST(@Value AS BIGINT), 
            0)
ORDER BY
    COUNT(Something) DESC

或者提前将变量转为BIGINT，让代码更清晰：

DECLARE @ValueBigInt BIGINT = @Value;

SELECT TOP 200   
    DATEADD(SECOND, 
            (DATEDIFF_BIG(SECOND, 0, TIMESTAMP) / @ValueBigInt) * @ValueBigInt, 
            0) AS [TIMESTAMP],
    COUNT(Something) AS [# of Sessions] 
FROM
    table WITH (NOLOCK)
WHERE
    TIMESTAMP BETWEEN @Start AND @End  
GROUP BY
    DATEADD(SECOND, 
            (DATEDIFF_BIG(SECOND, 0, TIMESTAMP) / @ValueBigInt) * @ValueBigInt, 
            0)
ORDER BY
    COUNT(Something) DESC

方案2：SQL Server 2016之前的版本

旧版本DATEADD(SECOND, ...)不支持BIGINT参数，可利用datetime的浮点特性计算分组时间，避免整数溢出：

SELECT TOP 200   
    CAST(
        FLOOR(CAST(TIMESTAMP AS FLOAT) * (86400.0 / @Value)) 
        / (86400.0 / @Value) 
        AS DATETIME
    ) AS [TIMESTAMP],
    COUNT(Something) AS [# of Sessions] 
FROM
    table WITH (NOLOCK)
WHERE
    TIMESTAMP BETWEEN @Start AND @End  
GROUP BY
    CAST(
        FLOOR(CAST(TIMESTAMP AS FLOAT) * (86400.0 / @Value)) 
        / (86400.0 / @Value) 
        AS DATETIME
    )
ORDER BY
    COUNT(Something) DESC

错误原因说明 #

1. 对于1968年之后的时间，DATEDIFF(SECOND, 0, TIMESTAMP)返回的INT值会超出INT类型最大值（约21亿秒），触发溢出；
2. 即使改用DATEDIFF_BIG返回BIGINT，若SQL Server版本低于2016，DATEADD(SECOND, BIGINT, 0)不被支持，会强制隐式转换为INT导致溢出；
3. 2016+版本中，若@Value为INT类型，运算时会自动将BIGINT结果转回INT，同样触发溢出，因此需要强制转换@Value为BIGINT。

内容的提问来源于stack exchange，提问作者freakostudent