SQL 从 Bigint 转换为日期时间值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3650320/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert From Bigint to datetime value
提问by Chris
please help me with this one, i want to convert a value from Bigint to datetime. For example im reading HISTORYtable of teamcity server, on the field build_start_time_serveri have this value on one record 1283174502729.
请帮我解决这个问题，我想将一个值从 Bigint 转换为 datetime。例如，我正在阅读teamcity server 的HISTORY表，在build_start_time_server字段上，我在一条记录 1283174502729 上有这个值。
How can i convert to datetime value???
我如何转换为日期时间值？？？
Thanks
谢谢
回答by Rob
Does this work for you? It returns 30-8-2010 13:21:42 at the moment on SQL Server 2005:
这对你有用吗？它目前在 SQL Server 2005 上返回 30-8-2010 13:21:42：
select dateadd(s, convert(bigint, 1283174502729) / 1000, convert(datetime, '1-1-1970 00:00:00'))
I've divided by 1000 because the dateadd function won't work with a number that large. So you do lose a little precision, but it is much simpler to use.
我除以 1000 是因为 dateadd 函数不能处理这么大的数字。所以你确实失去了一点精度，但使用起来要简单得多。
回答by Matt
Slightly different approach:
略有不同的方法：
Your scenario:
你的场景：
SELECT dateadd(ms, 1283174502729 / 86400000, (1283174502729 / 86400000) + 25567)
FROM yourtable
Generic code:
通用代码：
SELECT dateadd(ms, yourfield / 86400000, (yourfield / 86400000) + 25567)
FROM yourtable
Output:
输出：
August, 30 2010 00:00:14
SQL Fiddle: http://sqlfiddle.com/#!3/c9eb5a/2/0
SQL小提琴：http://sqlfiddle.com/#!3/c9eb5a/2/0
回答by stinkyjak
CAST(SWITCHOFFSET(CAST(dateadd(s, convert(bigint, [t_stamp]) / 1000, convert(datetime, '1-1-1970 00:00:00')) AS DATETIMEOFFSET), DATENAME (TZoffset, SYSDATETIMEOFFSET())) AS DATETIME)
回答by Arthur Teter
The following takes new SQL terminology into account and will return the milliseconds (can also be modified for use in a calculated field.) [SQL Server 2012 or later]
以下考虑了新的 SQL 术语并将返回毫秒（也可以修改以用于计算字段。）[SQL Server 2012 或更高版本]
declare @StartDate datetime2(3) = '1970-01-01 00:00:00.000'
, @milliseconds bigint = 1283174502729
, @MillisecondsPerDay int = 60 * 60 * 24 * 1000 -- = 86400000
SELECT DATEADD(MILLISECOND, TRY_CAST(@milliseconds % @millisecondsPerDay AS
INT), DATEADD(DAY, TRY_CAST(@milliseconds / @millisecondsPerDay AS INT),
@StartDate));
回答by user6710275
DATEADD(second,YourValue, CAST('1970-01-01 00:00:00' AS datetime))
回答by tarcisiorosa
Did you try FROM_UNIXTIME?
你试了FROM_UNIXTIME吗？
select from_unixtime('your_field') from 'your_table'
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_from-unixtime
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_from-unixtime
Works for me.
对我来说有效。
