mssql bigint unix timestamp 到 datetime 以毫秒为单位-百家乐凯发k8

问题描述

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

i have some timestamps that are in bigint. here's one:

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

mssql bigint unix timestamp 到 datetime 以毫秒为单位-百家乐凯发k8

问题描述

推荐答案