16

I having an column of UNIX time stamp in my database table, which comes from a system that is in the Kuwait time zone.

My database server's time zone is Eastern Time US & Canada. Now I need to convert the UNIX time stamp in to Kuwait time zone date value using an SQL query.

Can anyone tell me how I can convert this UNIX time stamp into a Kuwait time zone date value?

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
Ashish Rathore
  • 2,546
  • 9
  • 55
  • 91

1 Answers1

28

Unix timestamps are integer number of seconds since Jan 1st 1970 UTC.

Assuming you mean you have an integer column in your database with this number, then the time zone of your database server is irrelevant.

First convert the timestamp to a datetime type:

SELECT DATEADD(second, yourTimeStamp, '1970-01-01')

This will be the UTC datetime that corresponds to your timestamp.

Then you need to know how to adjust this value to your target time zone. In much of the world, a single zone can have multiple offsets, due to Daylight Saving Time.

Unfortunately, SQL Server has no ability to work work time zones directly. So if you were, for example, using US Pacific time, you would have no way of knowing if you should subtract 7 hours or 8 hours. Other databases (Oracle, Postgres, MySql, etc.) have built-in ways to handle this, but alas, SQL Server does not. So if you are looking for a general purpose solution, you will need to do one of the following:

  • Import time zone data into a table, and maintain that table as time zone rules change. Use that table with a bunch of custom logic to resolve the offset for a particular date.

  • Use xp_regread to get at the Windows registry keys that contain time zone data, and again use a bunch of custom logic to resolve the offset for a particular date. Of course, xp_regread is a bad thing to do, requires certain permissions granted, and is not supported or document.

  • Write a SQLCLR function that uses the TimeZoneInfo class in .Net. Unfortunately, this requires an "unsafe" SQLCLR assembly, and might cause bad things to happen.

IMHO, none of these approaches are very good, and there is no good solution to doing this directly in SQL. The best solution would be to return the UTC value (either the original integer, or the datetime at UTC) to your calling application code, and do the timezone conversion there instead (with, for example, TimeZoneInfo in .Net or similar mechanisms in other platforms).

HOWEVER - you have lucked out in that Kuwait is (and always has been) in a zone that does not change for Daylight Saving Time. It has always been UTC+03:00. So you can simply add three hours and return the result:

SELECT DATEADD(hour, 3, DATEADD(second, yourTimeStamp, '1970-01-01'))

But do recognize that this is not a general purpose solution that will work in any time zone.

If you wanted, you could return one of the other SQL data types, such as datetimeoffset, but this will only help you reflect that the value is three hours offset to whomever might look at it. It won't make the conversion process any different or better.

Updated Answer

I've created a project for supporting time zones in SQL Server. You can install it from here. Then you can simply convert like so:

SELECT Tzdb.UtcToLocal('2015-07-01 00:00:00', 'Asia/Kuwait')

You can use any time zone from the IANA tz database, including those that use daylight saving time.

You can still use the method I showed above to convert from a unix timestamp. Putting them both together:

SELECT Tzdb.UtcToLocal(DATEADD(second, yourTimeStamp, '1970-01-01'), 'Asia/Kuwait')

Updated Again

With SQL Server 2016, there is now built-in support for time zones with the AT TIME ZONE statement. This is also available in Azure SQL Database (v12).

SELECT DATEADD(second, yourTimeStamp, '1970-01-01') AT TIME ZONE 'Arab Standard Time'

More examples in this announcement.

Community
  • 1
  • 1
Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • I use this query and it works for me. SELECT CAST(SWITCHOFFSET (CAST(dateadd(s,1356052117,'01/01/1970') AS datetimeoffset), '+03:00') AS DATETIME) AS [Date] – Ashish Rathore Jun 03 '13 at 12:19
  • 2
    Well, switching the offset is good - if you are going to work with `DateTimeOffset` in your application. But if you are just casting back to a `datetime`, then there's no real added benefit. You might as well just add 3 hours. – Matt Johnson-Pint Jun 03 '13 at 14:57
  • Also - I see you are casting to `date` at the end. That will truncate any time component. Unless this is what you wanted, you should cast to `datetime` instead. – Matt Johnson-Pint Jun 03 '13 at 14:57
  • 1
    See I'm casting to `DateTime` not in `Date`.`Date` is the column name. – Ashish Rathore Sep 07 '13 at 07:52
  • I see now. I missed that before somehow. – Matt Johnson-Pint Sep 07 '13 at 14:58
  • Omg thank you Matt!! I've been breaking my head for the past 2 days. It's all because of the daylight savings time! I'm in NY time and I saw it kept jumping from 4 to 5. Here's a graph for those who are confused also http://www.timeanddate.com/time/zone/usa/new-york – Robert Sinclair May 31 '16 at 15:34