将纪元转换为DateTime SQL Server(超过2038年) [英] Convert Epoch to DateTime SQL Server (Exceeds Year 2038)

问题描述

如果纪元超过2038年，如何将纪元转换为DateTime SQL Server?

将纪元转换为DateTime SQL Server 中的答案将不起作用. /p>

示例:

SELECT DATEADD(ss, 2713795200000 / 1000, '19700101')

2055年12月30日，星期四，格林尼治标准时间

解决方案

DATEADD函数将INT假定为日期的增量，要绕过INT的限制，您可以降低时元的精度，也可以稍微复杂一些代码以保持您时代的准确性.

这会将精度降低到分钟:

SELECT DATEADD(MINUTE,@YourEpoch/60/1000, '1/1/1970')

这将您的时期分为几天和几毫秒，然后将它们合并为一个日期时间

CREATE FUNCTION [dbo].[fn_EpochToDatetime] (@Epoch BIGINT)
RETURNS DATETIME
AS
BEGIN
    DECLARE @Days AS INT, @MilliSeconds AS INT
    SET @Days = @Epoch / (1000*60*60*24)
    SET @MilliSeconds = @Epoch % (1000*60*60*24)

    RETURN (SELECT DATEADD(MILLISECOND, @MilliSeconds, DATEADD(DAY, @Days, '1/1/1970')))
END;

但是，我不确定第二种解决方案为什么不如我期望的那么精确.

How to convert Epoch to DateTime SQL Server if epoch exceeds the year 2038?

Answer in Convert Epoch to DateTime SQL Server will not work.

Example:

SELECT DATEADD(ss, 2713795200000 / 1000, '19700101')

Thu, 30 Dec 2055 16:00:00 GMT

解决方案

DATEADD function assumes an INT as an increment to your date, to bypass the limitation of INT you can either reduce the precision of your epoch, or do a slightly complex code to retain the precision of your epoch.

This reduces the precision to minutes:

SELECT DATEADD(MINUTE,@YourEpoch/60/1000, '1/1/1970')

This one splits your epoch to days and milliseconds and then combines them in a datetime

CREATE FUNCTION [dbo].[fn_EpochToDatetime] (@Epoch BIGINT)
RETURNS DATETIME
AS
BEGIN
    DECLARE @Days AS INT, @MilliSeconds AS INT
    SET @Days = @Epoch / (1000*60*60*24)
    SET @MilliSeconds = @Epoch % (1000*60*60*24)

    RETURN (SELECT DATEADD(MILLISECOND, @MilliSeconds, DATEADD(DAY, @Days, '1/1/1970')))
END;

However, I'm not quite sure why the 2nd solution is not as precise as I expect it to be.

这篇关于将纪元转换为DateTime SQL Server(超过2038年)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！