在sql server中从时区到时区的日期时间转换 [英] Date time conversion from timezone to timezone in sql server

问题描述

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?

解决方案

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.

这篇关于在sql server中从时区到时区的日期时间转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！