MSSQL bigint Unix Timestamp to Datetime with milliseconds
I think that you are dealing with nanosecond precision. What you can get in native sql is 100ns precision.
declare @ts as bigint = 1462924862735870900
select dateadd(NANOSECOND, @ts % 1000000000, dateadd(SECOND, @ts / 1000000000, cast('1970-01-01' as datetime2(7))))
The outcome is 2016-05-11 00:01:02.7358709
Sequenzia
Updated on June 14, 2022Comments
-
Sequenzia about 2 years
I have some timestamps that are in bigint. Here's one:
1462924862735870900
This is down to microsecond precision.
I am currently using this:
SELECT DATEADD(S, CONVERT(int,LEFT(1462924862735870900, 10)), '1970-01-01')
That's giving me datetime down to the second but I would like to maintain at least millisecond precision.
I realize that DATEADD cannot handle bigint that's why I truncated the bigint and converted it to int. If I don't do that I get this error:
Arithmetic overflow error converting expression to data type int
I'm hoping someone can help me figure out a better way to convert this and maintain at least millisecond precision.
Any help would be greatly appreciated. Thanks!
---- UPDATE ------
With the help of @ako, I threw together a function that takes a bigint timestamp in either milliseconds, microseconds or nanoseconds and returns DATETIME2(7) which is 100 nanosecond precision. It could probably be more efficient but here's the function:
CREATE FUNCTION [dbo].[fn_tsConvert] (@ts bigint) RETURNS DATETIME2(7) AS BEGIN DECLARE @ts2 DATETIME2(7) -- MILLISECOND IF(LEN(@ts) = 13) SET @ts2 = DATEADD(HH,-4,DATEADD(MILLISECOND, @ts % 1000, DATEADD(SECOND, @ts / 1000, CAST('1970-01-01' as datetime2(7))))) -- MICROSECOND IF(LEN(@ts) = 16) SET @ts2 = DATEADD(HH,-4,DATEADD(MICROSECOND, @ts % 1000000, DATEADD(SECOND, @ts / 1000000, CAST('1970-01-01' as datetime2(7))))) -- NANOSECOND IF(LEN(@ts) = 19) SET @ts2 = DATEADD(HH,-4,DATEADD(NANOSECOND, @ts % 1000000000, DATEADD(SECOND, @ts / 1000000000, CAST('1970-01-01' as datetime2(7))))) RETURN @ts2 END
-
Sequenzia about 8 yearsI think you are right. I didn't realize that was nanosecond. All of the converters I put it through online only went down to microsecond. Thanks for pointing that out and thanks for your solution. Seems to be working perfect. Thanks again!
-
Jose over 5 yearsI could adapt this to Milliseconds:
select dateadd(MILLISECOND, @ts % 1000, dateadd(SECOND, @ts / 1000, cast('1970-01-01' as datetime2(7))))