How to correctly convert Unix Timestamp to datetime in SQL

Question

I'm trying to get the date format dd/MM/YYYY or anything readable. actually I have the date data with this format stocked in a string field "1620305130000" I have tried the following

CONVERT(float, floor( CreationDate ) , 101)

convert(varchar, CreationDate ,101)

CONVERT(datetime, floor(CreationDate))

CONVERT(datetime, CreationDate)

but it didn't work

Thank you

Answer 1

First, let's understand what a Unix Timestamp is - it's the amount of time that has passed from 1970-01-01. This means that we need to add your CreationDate value to this starting date and we'll get the result.

We need to use the DATEADD function and here we encounter the biggest problem - your data type is mismatched: the function only accepts integers and your data provides a string that is a number that's bigger than the largest integer accepted by the SQL Server running in the back-end. Clearly that's not going to fly, so we need to work around this:

1. We convert this string to a decimal value that allows us to to store longer numbers: Convert(decimal(13,0), CreationDate)
2. Then we divide the number by 1000 (since the last three characters of you CreationDate represent miliseconds): Convert(decimal(13,0), CreationDate) / 1000
3. Next we convert this to an integer required by the DATEADD function: Convert(int, Convert(decimal(13,0), CreationDate) / 1000)
4. Finally, we can just add our new integer as a number of seconds (s) to the starting date: DateAdd(s, Convert(int, Convert(decimal(13,0), CreationDate) / 1000), '1970-01-01')

This should already output something that's readable to us, humans, but if you want to format it further, better include this transformation in a subquery and later just CONCAT different DATEPARTS

Answer 2

You've got to be really careful here. Most answers that use "/1000" will get you out of the woods for an immediate need but they fall apart because of the INT limit on DATEADD just 1 second after the date of “2038-01-19 03:14:07”. It's know as the "2038 problem" or, more affectionately by us old timers, the Y2K38 problem.

If we count the number of milliseconds in a day as 246060*1000, we end up with the very stable and "integer friendly" number of 86400000 and that's less than an integer.

If we take the difference in days between the UNIX Epoch of '1970-01-01' or just '1970' for short and the maximum date in SQL Server for a DATETIME2(3) ...

SELECT DATEDIFF(dd,'1970','9999-12-31 23:59:59.999')

We get the answer of 2932896 days, which is also "integer friendly".

We also have to overcome the fact that DATEDIFF() really likes to convert string representations of dates to DATETIME and we really need our answer to be in terms of the DATETIME2(3) datatype because DATETIME has the nasty habit of rounding 9 milliseconds up to the next temporal unit. Dates before the UNIX Epoch use negative timestamp values that also work in a similar but reverse manner)

So, that means that we have to do a little bit more that working with whole seconds and milliseconds in our conversion formula. The problem formula for BIGINT UNIX Timestamps that are based on milliseconds is...

SELECT  DATEADD(ms,@msUnixTS%86400000,DATEADD(dd,@msUnixTS/86400000,CONVERT(DATETIME2(3),'1970')))

The division (/) is integer math (in this case because both parts are integers of one type or another) that calculates the number of whole days as the quotient and the modulus operator (%) calculates the remainder as the number of remaining milliseconds not included in a whole day, which is the time of day.

Since you have the added complicity of the UNIX Timestamp being string based instead of being a BIGINT, we need to do an extra conversion. Adding all of that together with a variable for the original parameter that you provided, we get the following...

DECLARE  @msUnixTS VARCHAR(20) = '1620305130000'
;        --         |<---------- The TIME ---------->| |<------------------------------ The DATE -------------------------------->|
 SELECT  DATEADD(ms,CONVERT(BIGINT,@msUnixTS)%86400000,DATEADD(dd,CONVERT(BIGINT,@msUnixTS)/86400000,CONVERT(DATETIME2(3),'1970')))
;

And that's pretty much bullet proof for just about any form of a millisecond based UNIX Timestamp and doesn't suffer from the Y2K38 problem that other formulas based on seconds/milliseconds has.

It won't work for braille renditions, though. :D