Convert current Epoch time to Local time in SQL Server
Epoch, or Unix time, is a measure of time represented by the number of seconds since midnight on January 1, 1970 (UTC). It is used in various places, especially in POSIX systems such as Unix, Linux, BSD, etc. I recently came across an integration project where I had the need to convert current epoch time to the ‘local’ timezone in SQL Server. Here is what I ended up with.
DECLARE @epoch int
SET @epoch = 1342189899 --7/13/2012 14:31:39 UTC
PRINT DATEADD(minute, DATEDIFF(minute, getutcdate(), getdate()), DATEADD(s, @epoch, '19700101 00:00:00:000'))
--output: Jul 13 2012 9:31AMSee it in action on SQL Fiddle here: http://sqlfiddle.com/#!3/d41d8/2617/0
Note: The above approach works well for ‘current’ epoch timestamps but is flawed if you are trying to convert historical epoch times, because of the daylight savings time (DST) factor. The UTC offset calculation above (DATEDIFF(minute, getutcdate(), getdate())) is relying upon current system time and is not DST aware. In my particular case this is not an issue because I have an external system that is writing new records with a ‘current epoch timestamp’ so I convert them to the local timezone soon thereafter and store the result.