0

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?

Stefan Meeuwessen
  • 15
  • 5
  • Why not store the data using the correct type from the start? Eg `datetime2(3)` or `datetimeoffset(3)`? That's the easiest solution and the conversion can be done when loading the data, when the correct offset is known. `+2` may be the correct offset for Central Europe today, but after 1 month it will be `+1` – Panagiotis Kanavos Sep 22 '22 at 14:23
  • Don't do the conversion within SQL, use whatever external tools/language/script you are already using since it's quite likely that ability and more is already there and can handle things like DST. – Bib Sep 22 '22 at 14:23
  • Not possible for me, I use a GUI that displays certain data from an SQL database in a datagridview (C# WinForms) so I need to get the data "Perfect" in SQL it self. – Stefan Meeuwessen Sep 22 '22 at 14:29
  • Another possible way I am able to do this is via this query; `SELECT TOP(100) DATEADD(SECOND, FLOOR([AU_ACTIVITYSTARTTIME]/1000 +7200), '1970-01-01')` However, as you can see I manually added 7200 seconds... Which is a bad way to do this. – Stefan Meeuwessen Sep 22 '22 at 14:31
  • In that case make sure the data uses the correct types and offsets. Otherwise you're just assuming what the real offset is. Is it UTC? Local in Central Europe? East? Right now I'm working from Greece on a database in Sweden over VPN, so guess what I see when I run `select switchoffset(getdate(),2),getdate(),getdate() at TIME ZONE 'Central European Standard Time',getdate() at TIME ZONE 'E. Europe Standard Time';`. – Panagiotis Kanavos Sep 22 '22 at 14:32
  • Again - use the correct type. Otherwise **assuming** the data is local time, the safest option is to use `AT TIME ZONE '...'`, eg `SELECT getdate() at TIME ZONE 'Central European Standard Time'`. This will use the correct setting for DST and return a `datetimeoffset` with the correct offset for your timezone – Panagiotis Kanavos Sep 22 '22 at 14:35
  • Also beware of the Y2038 bug. You won't be able to just add seconds to `1970-01-01` because `DATEPART` uses an integer. The maximum int value is 2147483647 and `select dateadd(s,2147483647,'19700101')` produces `2038-01-19 03:14:07.000` – Panagiotis Kanavos Sep 22 '22 at 14:41
  • @PanagiotisKanavos Would you happen to know how I can implement this with either of my code examples?; Example 1: `SELECT TOP(100) [unixcolumn], CAST(DATEADD(ms, CAST(RIGHT([unixcolumn],3) AS SMALLINT), DATEADD(s, [unixcolumn] / 1000, '1970-01-01')) AS DATETIME2(3))` or `SELECT TOP(100) DATEADD(SECOND, FLOOR([unixcolumn]/1000), '1970-01-01')` – Stefan Meeuwessen Sep 22 '22 at 14:46
  • [This answer](https://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server) shows how to handle overflow - split the timestamp into years and seconds using division and modulo, then add first the years, then the seconds – Panagiotis Kanavos Sep 22 '22 at 14:46
  • @StefanMeeuwessen even if you absolutely can't use the correct data type (VERY BIG **WHY?** That's a **major** bug), it will be a *lot* easier and safer to not mix up the type conversion and offset switch. Add a computed column or use a subquery that calculates the date *first*, then use `AT TIME ...` to get a `datetimeoffset` in the expected timezone. – Panagiotis Kanavos Sep 22 '22 at 14:49
  • @StefanMeeuwessen what is the offset of the timestamp? Is it UTC? Local CEST time? `datetime` has no offset so `AT TIME ZONE` will treat it as local. If it's UTC, you'll have to convert it to a UTC datetimeoffset first before switching to CEST, eg `todatetimeoffset(getdate(),0) at TIME ZONE 'Central European Standard Time'`. For example, `DateAdd(SECOND, @t % 31622400, DateAdd(YEAR, @t / 31622400, '1970-01-01') as tm` will safely generate a date. Then `tm AT TIME ZONE 'Central European Standard Time` or `TODATETIMEOFFSET(tm,0) AT TIME ZONE 'Central European Standard Time'` – Panagiotis Kanavos Sep 22 '22 at 14:59
  • And how would I implement this? When I try and put a SELECT before this and change out the unix time stamps this query already has for my column that holds my unix timestamps, I just get Incorrect syntax everywhere. Would you happen to know how I could implement it correctly? I must be doing something wrong. – Stefan Meeuwessen Sep 23 '22 at 07:54

1 Answers1

0

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.

Stefan Meeuwessen
  • 15
  • 5