Convert epoch to datetime

Question

On a table I have a bigint column that stores a timestamp with a microsecond precision like:

636453251396217655
636453251398405201
636453251592389899
636453251668326820

I have to build a script that, if that date is older than a week, the row must moved to another table.

I tried to convert to date using:

CREATE FUNCTION [dbo].[UNIXToDateTime] (@timestamp bigint)
        RETURNS datetime
AS
BEGIN
        DECLARE @ret datetime

        SELECT @ret = DATEADD(second, @timestamp, '1970/01/01 00:00:00')

        RETURN @ret
END

and used like:

select dbo.UNIXToDateTime(636453251396217655)

but because of the bigint my script crash because:

Arithmetic overflow error during expression conversion in int data type

I can lose precision, the important is the date part that is the main part of the sql filter.

Demo: http://sqlfiddle.com/#!6/24f05/1

Possible duplicate of [Convert unix epoch timestamp to TSQL datetime](https://stackoverflow.com/questions/14507649/convert-unix-epoch-timestamp-to-tsql-datetime) — Joe, Nov 11 '17 at 14:49
@DavidG they are valid, wrote from a real time system with a deep precision check them on https://www.epochconverter.com/ — Irvin Dominin, Nov 11 '17 at 15:14
@DavidG yep, in microseconds as stated in the first line of the question :-) — Irvin Dominin, Nov 11 '17 at 15:18
@IrvinDominin . . . As DavidG points out, your values are not valid Unix timestamp. You can convert them on the web yourself epochconverter.com. The leading digit of "6" is highly suspect for any normal date. — Gordon Linoff, Nov 11 '17 at 15:29

score 2 · Accepted Answer · answered Nov 11 '17 at 15:45

There's an answer here for converting with epoch values:

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;

You can use that function but simply divide your epoch values. As you are fine with the loss of fidelity, this will suit your needs perfectly. For example:

DECLARE @epoch BIGINT = 636453251396217655

SELECT dbo.[fn_EpochToDatetime](@epoch/100000)

Convert epoch to datetime

1 Answers1