MSSQL bigint Unix Timestamp 到 Datetime 以毫秒为单位

2021-09-10 00:00:00 tsql sql-server unix-timestamp

我有一些在 bigint 中的时间戳.这是一个:

I have some timestamps that are in bigint. Here's one:

1462924862735870900

1462924862735870900

这可以达到微秒级精度.

This is down to microsecond precision.

我目前正在使用这个:

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.

我意识到 DATEADD 无法处理 bigint，这就是我截断 bigint 并将其转换为 int 的原因.如果我不这样做，我会收到此错误:

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

在@ako 的帮助下，我拼凑了一个函数，该函数采用毫秒、微秒或纳秒为单位的 bigint 时间戳，并返回 DATETIME2(7)，即 100 纳秒精度.它可能更有效，但功能如下:

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

推荐答案

我认为您正在处理纳秒精度.您可以在原生 sql 中获得 100ns 的精度.

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

结果是 2016-05-11 00:01:02.7358709

The outcome is 2016-05-11 00:01:02.7358709

相关文章