Convert Epoch to DateTime SQL Server (Exceeds Year 2038)

Question

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

Answer 1

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.

Answer 2

Building on the response above, the solution provided works but does not protect from trying to convert to a date that is out of bounds for SQL server.

create function dbo.unixTimestampConversion ( @unixTime bigInt ) returns dateTime2(7) as begin

    declare
        @output dateTime2(7)
        , @days int
        , @ms   int
        , @x    int = (1000 * 60 * 60 * 24)
    ;


    set @days = @unixTime / @x
    ;
    set @ms = @unixTime % @x
    ;
    if (@unixTime < 32503593600000 and @unixTime > -2208988800000)
        begin
            set @output = dateAdd (millisecond, @ms, dateAdd (day, @days, '1/1/1970'))
            ;
        end
        ;
    else if (@unixTime <= -2208988800000)
             begin
                 set @output = '1/1/1900'
                 ;
             end
             ;
    else if (@unixTime >= 32503593600000)
             begin
                 set @output = '12/31/2999'
                 ;
             end
             ;
    return @output
    ;


end

;

Answer 3

You can assign the epoch time to your datetime directly (I tried this on SQL Server 15.0). Although it considers the number as the number of days since 1900-1-1 00:00:00 so you have to add 2208988800 (the number of seconds in 70 years) and then divide by 86400(number of seconds in a day).

DECLARE @time DATETIME = (2208988800.0 + [your epoch time in seconds])/86400;

However, it seems to be 0.007s or 0.003s behind the given epoch. Also, I'm not sure if this is faster than the DATEADD() function.

Answer 4

Old question, but later versions of SQL Server that support the DATETIMEOFFSET data type have made this easier:

DATEADD(CAST('1970-01-01T00:00:00Z' AS DATETIMEOFFSET),s,<epoch time as bigint>))

DATEADD can work with s/second, ms/millisecond, mcs/microsecond, and ns/nanosecond.

Then, if you need it in a specific time zone, use SELECT <date> AS TIME ZONE <timezone>.

Answer 5

create a function to convert epoch to datetime and use them in your query like below

create FUNCTION [dbo].[from_unixtime] (@Datetime BIGINT)
RETURNS DATETIME
AS
BEGIN
    DECLARE @LocalTimeOffset BIGINT
           ,@AdjustedLocalDatetime BIGINT;
    SET @LocalTimeOffset = DATEDIFF(second,GETDATE(),GETUTCDATE())
    SET @AdjustedLocalDatetime = @Datetime - @LocalTimeOffset
    RETURN (SELECT DATEADD(second,@AdjustedLocalDatetime, CAST('1970-01-01 00:00:00' AS datetime)))
END;

and then use this function in your query