SQL查询以从GMT将DB中的现有DateTime字段和值更新为PST

Question

我正在寻找编写一个查询的最佳方法，该查询采用数据库中现有的DateTime字段/值（超过50 K行），并将DateTime值更新到不同的时区。我想做的是将现有的日期/时间值从GMT时间（当前服务器时间）更改为PST时间（我认为应该是减去7小时）。我想更改数据库中的值，然后我会将服务器上的服务器时间更改为PST，以反映时区，这样所有新记录都显示PST时区。

如有任何帮助，我们将不胜感激，提前感谢您抽出时间。

Answer 1

您可能根本不应该这样做。在数据库中保留GMT（实际上是UTC）格式的数据，并根据需要在应用程序逻辑中将其转换为太平洋时间。例如，如果您从.NET应用程序连接到SQL Server，请从数据库中查询UTC时间，并使用TimeZoneInfo.ConvertTimeFromUtc 进行转换

最大的原因是太平洋时间使用夏令时。因此，虽然一年中的部分时间它遵循PST（即UTC-8），但在夏季它遵循PDT（即UTC-7）。这意味着调整的时间量是变量，并且取决于日期本身。

不仅如此，在本地时间，在回退转换期间出现的值是不明确的。例如，2015-11-01在太平洋时间凌晨1:30出现两次。如果您将所有数据转换为太平洋时间，您可能会丢失一些信息。

如果确实需要直接在SQL Server中在时区之间转换，请考虑使用我的SQL Server时区支持项目。例如：

SELECT Tzdb.UtcToLocal('2015-07-01 00:00:00', 'America/Los_Angeles')

Answer 2

遗憾的是，SQL Server没有任何内置的时区转换功能。但是，这可以通过SQLCLR来实现。可以使用DateTime.ToLocalTime（）方法创建标量用户定义函数。

是的，从技术上讲，您可以使用TimeZoneInfo类，该类具有ConvertTime(DateTime, TimeZoneInfo, TimeZoneInfo)和ConvertTimeFromUtc(DateTime, TimeZoneInfo)等方法，但TimeZoneInfo类要求程序集的PERMISSION_SET为UNSAFE，而DateTime可以作为SAFE运行。坚持使用DateTime方法的唯一缺点是，它们只能在UTC和服务器的本地时间之间转换，而TimeZoneInfo可以在任意两个给定时区之间转换。

由于您的数据已经是GMT/UTC，因此使用DateTime.ToLocalTime，转换为太平洋时间是相当容易的，但只有在更新数据之前而不是之后（如问题中所述）更改服务器的时区。在这一点上，你可以做以下事情：

ALTER TABLE [SchemaName].[TableName]
  ADD [IsConverted] BIT NULL; -- make sure we don't convert multiple times
WHILE (1 = 1)
BEGIN
  UPDATE TOP (2000) tab
  SET    tab.DateField = dbo.ConvertTimeToLocalTime(tab.DateField),
         tab.IsConverted = 1
  FROM   [SchemaName].[TableName] tab
  WHERE  tab.IsConverted IS NULL;
  IF (@@ROWCOUNT = 0)
  BEGIN
    BREAK;
  END;
END;
-- Remove the temporary tracking field, but not until all rows are converted
IF (NOT EXISTS(
               SELECT *
               FROM   [SchemaName].[TableName] tab
               WHERE  tab.IsConverted IS NULL
              )
    )
BEGIN
  ALTER TABLE [SchemaName].[TableName]
    DROP COLUMN [IsConverted];
END;

*请注意我使用了"太平洋"时间而不是太平洋标准时间，因为太平洋时间可以是太平洋标准时间或太平洋标准时间（取决于夏令时）。因此，尝试通过简单地执行DATEADD(HOUR, -7, [DateField])进行转换对于某些行是正确的，但不是所有行。

此外，对于那些对这个特定转换函数感兴趣的人来说，它可以在SQL#库的完整版本中获得（尽管不是免费的）（我是该库的作者）。有一些博客文章展示了如何做到这一点，尽管我还没有看到一篇遵循最佳实践的博客文章。当然，如果这是一次性转换，那么价格的最佳性能无疑不如效率较低但免费的重要：-）。

Answer 3

您可以使用ALTER database语句的SET time_zone子句更改数据库时区。例如：

ALTER DATABASE SET TIME_ZONE='Europe/London';ALTER DATABASE SET TIME_ZONE='-05:00';

您可以参考以下链接了解更多详细信息。http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG263