将bigint转换为datetime

Question

我想把一个bigint类型的值转换成datetime类型。

例如，我正在读取TeamCity服务器的HISTORY表。在字段build_start_time_server中，我有一条记录上的值为1283174502729。

我该如何将它转换为datetime值呢？

- Chris

---

我更改了标签，希望使它们更具体。请注意，如果您在标签字段中使用“sql server”，则该内容将归类于sql标签和server标签，但不会归类于sql-server标签，这可能是您想要的。 - krock

你想要1283174502729对应的日期时间数值是什么？ - gbn

同意@krock的观点。由于您的问题不特定于您想要使用的SQL实现（例如：sql-server），因此这里有很多答案取决于SQL的类型而不适用。 - hlin117

Answer 1

这个方法对你有用吗？目前在SQL Server 2005中返回的是30-8-2010 13:21:42：

select dateadd(s, convert(bigint, 1283174502729) / 1000, convert(datetime, '1-1-1970 00:00:00'))

我将数字除以1000是因为dateadd函数无法处理如此大的数字。因此，您会失去一些精度，但使用起来更简单。

- Rob

---

第二个参数是INT，不是BIGINT。也就是说，去掉CONVERT。没有必要。 - R.Alonso

Answer 2

稍微有些不同的方法：

您的情景：

SELECT dateadd(ms, 1283174502729 / 86400000, (1283174502729 / 86400000) + 25567)
FROM yourtable

通用代码：

SELECT dateadd(ms, yourfield / 86400000, (yourfield / 86400000) + 25567)
FROM yourtable

输出：

August, 30 2010 00:00:14

SQL Fiddle: http://sqlfiddle.com/#!3/c9eb5a/2/0

- Matt

Answer 3

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)

- stinkyjak

---

我仍然不理解投票下降。是否存在错误？这适用于2008R2。它是否低效？如果我不知道该如何改进，该怎么办呢？ - stinkyjak

我想这个踩的原因是你只提供了代码答案，没有解释它在做什么或为什么有效。 - iamdave

Answer 4

下面将考虑新的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));

- Arthur Teter

Answer 5

将bigint转换为datetime/unixtime时，必须在转换为时间戳之前将这些值除以1000000（10e6）。

SELECT
     CAST( bigIntTime_column / 1000000 AS timestamp) example_date
FROM example_table

简单易用的解决方案，不需要导入任何额外的库或函数

- whitetiger1399

---

这个解决方案更简单，而且对我来说效果更好。谢谢。 - Abdessamad139

Answer 6

如果您想保持毫秒级的精度，可以按照以下步骤进行操作。适用于SQL Server 2016。

SELECT dateadd(ms, ((CONVERT(bigint, build_start_time_server)%1000)), 
       dateadd(ss, ((CONVERT(bigint, build_start_time_server)/1000)%60), 
       dateadd(mi, ((CONVERT(bigint, build_start_time_server)/1000)/60), '1970-01-01'))) FROM yourtable

我得到的答案是：

Monday, August 30, 2010 1:21 PM

- Harikrishnan mk

Answer 7

选择 Cast(Cast(19980324 as nvarchar) as Datetime)

- Ajit Medhekar

Answer 8

DATEADD(second,YourValue, CAST('1970-01-01 00:00:00' AS datetime))

- user6710275

---

使用问题中提供的数据时，出现“将表达式转换为数据类型int时算术溢出错误”的负分。 - UnhandledExcepSean

然而，稍加调整后它可能会起作用，也许发布者可以再努力一点？（点赞以鼓励他一下 :) ） - Gar

Answer 9

你试过使用 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

对我来说可行。

- tarcisiorosa