MSSQL bigint Unix Timestamp to Datetime with milliseconds

Question

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

1462924862735870900

This is down to microsecond precision.

I am currently using this:

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.

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

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

Answer 1

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

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

Answer 2

If you work with any BIGINT UNIX TimeStamp later than "2038-01-19 03:14:07.000" or earlier than "1901-12-13 20:45:52.000", you're going to run into a serious issue with the currently accepted answer. Lets try one and see...

--===== Create the UNIX Timestamp from a future Date/Time.
DECLARE @TS AS BIGINT = DATEDIFF_BIG(ns,'1970','2038-01-19 03:14:08.000')
;
--===== Use the current accepted answer to try to convert back to a Date/Time
select dateadd(NANOSECOND, @ts % 1000000000, dateadd(SECOND, @ts / 1000000000, cast('1970-01-01' as datetime2(7))))
;

Results...

The problem is, while there is a DATEDIFF_BIG, there is no DATEADD_BIG and DATEADD is limited to just integers. That one extra second takes us to 2147483648000000000 for a BIGINT Timestamp. Dividing that by 1000000000 gives us 2147483648, which is +1 larger than the maximum value for an INT.

Instead of working with seconds and milliseconds, work with days and the number of milliseconds in a day. Like this... (fully documented function).

DO READ THE WARNING ABOUT CONVERSIONS TO THE DATETIME DATATYPE!!! There's a very short fix for that but I'm still testing to make sure.

Also, the following function is for BIGINT UNIX Timestamps in Milli-Seconds. If your UNIX Timestamp is in Micro-Seconds and you don't care about the Micros-Seconds, just divide the given time stamp by 1,000 and send the result to the function above. Same for UNIX Timestamps that are in Nano-Seconds but the number to divide by is 1,000,000.

 CREATE OR ALTER FUNCTION dbo.msUnixTStoDATETIME2
/****************************************************************************************
 Purpose: Given a "New" type of BIGINT UNIX Timestamp based on milliseconds, convert it
          to a DATETIME2(3).
***** WARNING ***** WARNING ***** WARNING ***** WARNING ***** WARNING ***** WARNING *****
*****        DO NOT USE THIS FUNCTION TO CONVERT TO THE DATETIME DATATYPE!!!        *****
*****     There will be horrendous rounding errors because of DATETIME rounding.    *****
***** WARNING ***** WARNING ***** WARNING ***** WARNING ***** WARNING ***** WARNING *****
-----------------------------------------------------------------------------------------
 Usage Examples:
--===== Basic Syntax
 SELECT TheDateTime
   FROM dbo.msUnixTStoDATETIME2(@msUnixTS)
;
--===== Convert a single hard-coded millisecond based UNIX Timetimestamp
     -- Converts to '2022-07-19 23:58:37.096'
 SELECT TheDateTime
   FROM dbo.msUnixTStoDATETIME2(1658275117096)
;
--===== Convert a column of millisecond based UNIX Timetimestamp to DATETIME2(3)
 SELECT dt.TheDateTime
   FROM dbo.SomeTable st
  CROSS APPLY dbo.msUnixTStoDATETIME2(st.msUnixTsColumn) dt
;
-----------------------------------------------------------------------------------------
 Performance: (on Alienware R 17 NVME SSD 32 GB RAM - SQL Server 2017 Developers Edition
 Test output dumped to throw away variable from a TempTable to remove display times.
         1,000 rows - CPU time =       0 ms,  elapsed time =       0 ms.
        10,000 rows - CPU time =       0 ms,  elapsed time =       3 ms.
       100,000 rows - CPU time =      31 ms,  elapsed time =      33 ms.
     1,000,000 rows - CPU time =     328 ms,  elapsed time =     329 ms.
    10,000,000 rows - CPU time =   3,250 ms,  elapsed time =   3,257 ms.
   100,000,000 rows - CPU time =  32,391 ms,  elapsed time =  32,428 ms.
 1,000,000,000 rows - CPU time = 319,218 ms,  elapsed time = 325,037 ms.
  
-----------------------------------------------------------------------------------------
 Ref Article: Convert UNIX Timestamps to DATETIMEs in SQL Server #1 by Jeff Moden
        SITE: SQLServerCentral.com
-----------------------------------------------------------------------------------------
 Revision History:
 Rev 00 - 09 Apr 2022 - Jeff Moden
                      - Proof of principle, unit testing.
 Rev 01 - 19 Jul 2022 - Jeff Moden
                      - Added final documentation.
****************************************************************************************/--
        (@msUnixTS BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN --                       |<-- The TIME -->| |<----------- The DATE ----------->|
 SELECT TheDateTime = DATEADD(ms,@msUnixTS%msPerDay,DATEADD(dd,@msUnixTS/msPerDay,Epoch))
   FROM (VALUES(86400000,CONVERT(DATETIME2(3),'1970')))v1(msPerDay,Epoch) --"DRY" Parts
;

To be sure, the non-"DRY" functionality (without the CROSS APPLY) is a tiny bit faster but I wanted the code itself to be self-documenting.