CreateTime bigint

Question

CreateTime bigint

reuvygroovy 781

We have an RDS deployment with a DB for the broker that manages it. There is a central DB and there when a user logs in in the Sessions table there is a column called CreateTime which is defined as a bigint:

For example, I entered in today at ~4:42PM and the record was 132500977560844177.

How can I convert this to a readable value?

Accepted answer

1 additional answer

Your answer

Answer 1

Hi @reuvygroovy ,

The key here is "UTCFILETIME" which contains a 64-bit value representing the number of 100-nanosecond intervals since January 1, 1601 (UTC).

If you are on SQL Server 2008 or later, you could refer below:

DECLARE @ts as bigint = 132500977560844177  
SELECT DATEADD(nanosecond,@ts % 600000000,  
DATEADD(minute,@ts / 600000000, cast('16010101' as datetime2(7))))

Output:

2020-11-17 14:42:00.3608442

If you are on an earlier version, or if there is another reason that you have to use datetime, you could refer below:

Declare @filetime_to_convert bigint  
Set @filetime_to_convert = 132500977560844177  
  
SELECT DATEADD(ms,    ((@filetime_to_convert-47966688000000000) % 600000000)/1000000,  
       DATEADD(minute,(@filetime_to_convert-47966688000000000) / 600000000, '17530101'))

Output:

2020-11-17 14:42:00.360

The magic value 47966688000000000 is the number of FILETIME units between 1601-01-01 and 1753-01-01. The breakdown in minutes and milliseconds (ms) is to prevent that the value overflows the maximum value of int (which would throw a runtime error for the function DateAdd()).

Reference:Convert BIGINT Timestamp to a Datetime???

Best regards
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Viorel 122.6K Reputation points

2020-11-18T11:26:22.257+00:00

Note that the seconds seem lost and the more correct result, with seconds, is “2020-11-17 14:42:36”. (This can be verified using DateTime in C# or VB, for example).

Answer 2

It seems that CreateTime is a FILETIME value (https://learn.microsoft.com/en-us/windows/win32/api/minwinbase/ns-minwinbase-filetime), which can be converted using these steps:

declare @CreateTime as bigint = 132500977560844177  
declare @microseconds as bigint = @CreateTime / 10   
declare @seconds as bigint = @microseconds / 1E6  
declare @minutes as bigint = @seconds / 60   
declare @hours as bigint = @minutes / 60   
declare @days as bigint = @hours / 24  
declare @remaining_milliseconds bigint = (@microseconds - @days * 24 * 60 * 60 * 1E6) / 1000   
  
declare @result as datetime2 = dateadd(d, @days, cast('1601/01/01' as datetime2))  
set @result = dateadd(ms, @remaining_milliseconds, @result)  
  
select @result -- shows '2020-11-17 14:42:36.0840000'

Share via

CreateTime bigint

1 additional answer

Your answer