sqlsql-serverunix-timestampepochdateadd

How to change timezone from GMT+0 to GMT+2 of this Unix epoch time (Milliseconds) conversion Query?

I have a database that stores Unix Epoch time stamps in milliseconds. I use this code to translate it to something readable (which I use in a GUI I'm working on). However this results in a data set in GMT+0, I happen to need GMT+2. Does anyone know how I can add those 2 hours to the result.

Also, does anyone know what I can do about lightsaving? Cause that would also make this result incorrect.

Code;

SELECT TOP(100) [unixcolumn], CAST(DATEADD(ms, CAST(RIGHT([unixcolumn],3) AS SMALLINT), 
DATEADD(s, [unixcolumn] / 1000, '1970-01-01')) AS DATETIME2(3))
FROM [db].[dbo].[table]

I've also tried other solutions like this;

SELECT DATEADD(s, LEFT([unixcolumn], LEN([unixcolumn]) - 3), '1970-01-01')
FROM [db].[dbo].[table]

To no avail.

So how can I add 2 hours to my first example? Or even better, set the timezone to CEST or GMT+2?

Solution

  • I found a way to do this and it works nicely for my specific needs. Here is my Query;

    LEFT(DATEADD(SECOND, FLOOR([epochtime_column]/1000 ), '1970-01-01') AT TIME ZONE 'UTC' at TIME ZONE 'Central European Standard Time', 19)

    You basically chop off the 3 added numbers from the epoch time in milliseconds to turn it into epoch time in seconds. After which you add those seconds up to 1970-01-01 to turn it into a readable date.

    This is only a viable strategy when you don't need the milliseconds from the epoch time. IF you do need the milliseconds then this solution will not work for you.