将Unix Epoch时间戳转换为TSQL日期时间

Question

我发现只有一个类似的MySQL问题。 我在开发网络服务时不得不查询数据库（MS SQL server）。由于我无法获得正确的结果，所以决定通过SQL客户端测试查询。Web服务使用Hibernate访问数据库，并且所有时间值始终表示为长整型值（Unix纪元时间）。为了测试它，我需要将Unix时间戳转换为TSQL时间戳。这就是我想出来的方法：

select dateadd(ms,123,'1970-01-01 00:00:00.0');

这将输出：

1970-01-01 00:00:00.123

但是，我的实际数据稍微大了一点

select dateadd(ms,1359016610667 ,'1970-01-01 00:00:00.0');

输出如下：

Error code 0, SQL state 22001: Data truncation
Error code 8115, SQL state 22003: Arithmetic overflow error converting expression to data type int.

所以，我尝试了：

select dateadd(ms,CAST (1359016610667 AS BIGINT) ,'1970-01-01 00:00:00.0');

这会输出完全相同的错误。为了安全起见，我尝试了：

select CAST (1359016610667 AS BIGINT) 

输出结果为：

1359016610667

我确保 java long 等同于 TSQL bigint - 它们都是 8 B 长。重新阅读 dateadd() documentation 后发现以下内容：

DATEADD (datepart , number , date )
....
number
是一个表达式，可以解析为 int，并添加到日期的 datepart 中。用户定义的变量是有效的。

如果我理解正确，这意味着这种方法不能用于将 Unix 时间戳转换为 TSQL 时间戳，这很愚蠢。

我的问题是：

• 我对这种情况的解释正确吗？
• 是否有其他一行代码可以在 TSQL 中完成此转换？

PS
修改日期参数（'1970-01-01 00:00:00.0'）不是可接受的解决方案。我正在调试，不想重新计算毫秒数 :)

- linski

---

请问给我点踩的人可以解释一下为什么要踩这个问题吗？ - linski

又是LoL？好吧，看看上面的评论... - linski

Answer 1

首先加上整天的时间，然后再加上剩余的毫秒数。一天有86,400,000毫秒。

declare @unixTS bigint
set @unixTS = 1359016610667


select dateadd(ms, @unixTS%(3600*24*1000), 
    dateadd(day, @unixTS/(3600*24*1000), '1970-01-01 00:00:00.0')
)

结果是 2013-01-24 08:36:50.667

- Ben

---

结果是2013-01-24 08:36:50.667，但是在哪个时区？UTC吗？ - luisdev

如果输入的 Unix 时间戳是 UTC 格式，那么输出结果也将是 UTC 格式。否则，则不会。 - Ben

Answer 2

这应该完美地适用于那些很长的纪元时间。

SELECT DATEADD(SECOND, 1359016610667 / 1000, '19700101 00:00')

- Scotty

---

使用仅添加秒的方法（而不是@ben答案中同时添加毫秒和天数的方法），当处理像“2099年12月31日”这样的日期时，会导致算术溢出错误，因为SQL尝试将要添加的秒数转换为int。 - user1098928

这将导致Y2038问题。'number'参数是整数，因此像这样添加秒仅限于2038-01-19。 - Panagiotis Kanavos