Unix 时间戳是自 UTC 1970 年 1 月 1 日以来的整数秒数.

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.

首先将时间戳转换为datetime类型:

First convert the timestamp to a datetime type:

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

这将是与您的时间戳对应的 UTC datetime.

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.

不幸的是，SQL Server 无法直接处理工作时区.因此，例如，如果您使用美国太平洋时间，您将无法知道应该减去 7 小时还是 8 小时.其他数据库(Oracle、Postgres、MySql 等)有内置的方法来处理这个问题，但遗憾的是，SQL Server 没有.因此，如果您正在寻找通用解决方案，则需要执行以下操作之一:

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.

使用 xp_regread 获取包含时区数据的 Windows 注册表项，并再次使用一堆自定义逻辑来解析特定日期的偏移量.当然，xp_regread 是一件坏事，需要授予某些权限，并且不被支持或文档.

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.

编写一个使用 .Net 中的 TimeZoneInfo 类的 SQLCLR 函数.不幸的是，这个需要一个不安全"的 SQLCLR 程序集，并且可能会导致不好的事情发生.

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.

恕我直言，这些方法都不是很好，并且没有直接在 SQL 中执行此操作的好的解决方案.***的解决方案是将 UTC 值(原始整数或 UTC 的 datetime)返回到您的调用应用程序代码，并在那里进行时区转换(例如，TimeZoneInfo .Net 或其他平台中的类似机制).

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).

然而 - 您很幸运，科威特(并且一直)处于夏令时不会改变的区域.一直是UTC+03:00.所以你可以简单地添加三个小时并返回结果:

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.

如果您愿意，您可以返回其他 SQL 数据类型之一，例如 datetimeoffset，但这只会帮助您反映该值与可能查看它的人之间的三个小时偏移.它不会使转换过程有任何不同或更好.

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.

更新答案

我创建了一个项目来支持 SQL Server 中的时区.您可以从这里安装它.然后你可以像这样简单地转换:

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')

您可以使用IANA tz 数据库中的任何时区，包括使用夏令时的时区时间.

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

您仍然可以使用我上面展示的方法从 unix 时间戳转换.将它们放在一起:

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')

再次更新

在 SQL Server 2016 中，现在内置了对时区的支持，AT TIME ZONE 语句.这也适用于 Azure SQL 数据库 (v12).

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'

本公告中的更多示例.