Convert From Bigint to datetime value

问题:

please help me with this one, i want to convert a value from Bigint to datetime. For example im reading HISTORY table of teamcity server, on the field build_start_time_server i have this value on one record 1283174502729.

How can i convert to datetime value???

Thanks

回答1:

Does this work for you? It returns 30-8-2010 13:21:42 at the moment on SQL Server 2005:

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.

回答2:

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

回答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)

回答4:

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]

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));

回答5:

Did you try 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

Works for me.

回答6:

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