将Unix纪元时间戳转换为TSQL日期时间 [英] Convert unix epoch timestamp to TSQL datetime
问题描述
我只发现了一个类似的问题但对于MySQL。
I have found only one similar question but for MySQL.
我正在使用Web服务，并且不得不查询数据库（MS SQL服务器）。由于无法获得正确的结果，我决定通过SQL客户端测试查询。 Web服务使用Hibernate访问数据库，并且所有时间值始终表示为长值（unix epoch time）。为了对其进行测试，我需要将unix时间戳转换为TSQL时间戳。这是我想出的：
I was working on a web service and had to query the database (MS SQL server). Since I couldn't get the right result I decided to test the query via a SQL client. The web service uses Hibernate to access the DB and all time values are always represented as long values (unix epoch time). In order to test it, I needed to convert the unix timestamp to TSQL timestamp. This is what I came up with:
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');
输出完全相同的错误。为了安全起见，我尝试过：
which outputs the exact same error. Just to be safe I tried:
select CAST (1359016610667 AS BIGINT)
会输出：
1359016610667
我确保 java long 等效于 TSQL bigint -它们都 8 B 长。重新阅读 dateadd（）文档后发现：
I made sure that java long is equivalent to TSQL bigint - they are both 8 B long. Rereading the dateadd() documentation revealed the following:
DATEADD（datepart，number，date）
....
数字
是一个表达式，可以解析为int ，并将其添加到日期的datepart中。用户定义的变量有效。
DATEADD (datepart , number , date )
....
number
Is an expression that can be resolved to an int that is added to a datepart of date. User-defined variables are valid.
如果我正确理解这一点，则意味着该方法不能用于转换unix时间戳到TSQL时间戳，这是原谅我的语言，但只是愚蠢。
If I understand this correctly, it means that this approach can not be used to convert a unix timestamp to TSQL timestamp, which is, well, pardon my language, but just plain stupid.
我的问题是：
- 我对这种情况的解释正确吗？
- 在TSQL中是否还有其他单行进行此转换？ / li>
- is my interpretation of this situation correct?
- is there any other one-liner to do this conversion in TSQL ?
PS
修改日期参数（'1970-01-01 00:00 ：00.0'）不能作为解决方案。我正在调试，并且不想重新计算毫秒数：）
PS
modifying the date argument ('1970-01-01 00:00:00.0') is not acceptable as solution. I'm debugging and I don't want to recalculate the miliseconds along :)
推荐答案
容易，首先添加整天，然后添加剩余的毫秒。一天有86,400,000毫秒。
Easy, first add whole days, then add the remaining ms. There are 86,400,000 milliseconds in a day.
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
这篇关于将Unix纪元时间戳转换为TSQL日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
