pls-toolbox_使用T-SQL Toolbox数据库解决时区，GMT和UTC问题

pls-toolbox

介绍 (Introduction)

Soon enough, the applications and database software we build will handle date and time values. The T-SQL Toolbox database – a free download – can help solve complex calculations with those values.

很快，我们构建的应用程序和数据库软件将处理日期和时间值。 T-SQL工具箱数据库（免费下载）可以帮助解决这些值所带来的复杂计算。

I live in the U.S., as do many of my customers. We must deal with daylight savings time, which means that on specific dates, the local time in a specific time zone can jump forward one hour, and on other dates, the local time can fall back one hour. These changes happen on a defined schedule. A forward change always follows a backward change, and a backward change always follows a forward change. They never accumulate. Dealing with all this in the database layer can become really complex, and T-SQL Toolbox can save a lot of effort with this. At a conference, I heard a speaker explain that we should use Greenwich Mean Time, or GMT, for database date and time values to avoid this issue. GMT never changes for daylight savings time, and we can easily calculate local time values based on GMT data. Although SQL Server offers plenty of built-in date and time functions, those functions don’t directly handle conversions between local time zone values and GMT, and they certainly don’t handle daylight savings time conversions. Fortunately, I found the T-SQL Toolbox as a way to avoid building the functions that I would need for all of this. Available here at the CodePlex Archive, and here at GitLab, the T-SQL Toolbox provides SQL Server user-defined functions (UDFs) that convert date/time values between time zones, including GMT. T-SQL Toolbox also provides UDFs that calculate starting or ending

我和许多客户一样都住在美国。 我们必须处理夏令时，这意味着在特定日期，特定时区中的本地时间可能会向前跳一小时，而在其他日期，则本地时间会后退一小时。 这些更改按定义的时间表进行。 前向更改始终跟随着后向更改，而后向更改始终跟随着前向更改。 他们从不积累。 在数据库层中处理所有这些事情可能会变得非常复杂，并且T-SQL Toolbox可以为此节省很多工作。 在一个会议上，我听到一位发言者解释说，我们应该对数据库日期和时间值使用格林威治标准时间 （GMT），以避免出现此问题。 GMT永远不会更改夏令时，因此我们可以根据GMT数据轻松计算本地时间值。 尽管SQL Server提供了大量内置的日期和时间函数，但是这些函数不能直接处理本地时区值和GMT之间的转换，并且它们当然不能处理夏令时转换。 幸运的是，我发现使用T-SQL工具箱来避免构建所有这些功能所需的功能。 可在这里在CodePlex上存档，并在这里在GitLab，在T-SQL工具箱提供的时区，包括GMT之间SQL Server用户定义函数（UDF），该转换日期/时间值。 T-SQL工具箱还提供了计算开始或结束的UDF。

• day
  天
• week
  周
• month
  月
• quarter
  25美分硬币
• year
  年

DateTime values for a given date/time value.

给定日期/时间值的DateTime值。

下载并安装 (Download and Install)

After download:

下载后：

and file extraction, as shown below:

和文件提取，如下所示：

As seen in the following screenshot, drill down into the sourceCode directory:

如以下屏幕截图所示，深入到sourceCode目录：

Then, drill down into the sourceCode.zip file. Copy the TSqlToolbox.sql file

然后，深入到sourceCode.zip文件。 复制TSqlToolbox.sql文件

We’ll run the TSqlToolbox.sql file to install the T-SQL Toolbox database in SQL Server. I installed it in a SQL Server 2014 Standard Edition environment on a Windows 10 PC.

我们将运行TSqlToolbox.sql文件以在SQL Server中安装T-SQL Toolbox数据库。 我将其安装在Windows 10 PC上SQL Server 2014 Standard Edition环境中。

The TSqlToolbox.sql file has a CREATE DATABASE script, and sets of CREATE TABLE and CREATE FUNCTION scripts. Open TSqlToolbox.sql in a SQL Server query window

TSqlToolbox.sql文件具有CREATE DATABASE脚本，以及CREATE TABLE和CREATE FUNCTION脚本集。 在SQL Server查询窗口中打开TSqlToolbox.sql

This will create the complete TSqlToolbox database, its tables, table data rows, functions, and primary/foreign key constraints. In the screenshot above, the FRANK-PC database server hosts the TSqlToolbox. Every database that the FRANK-PC database server hosts can use the TSqlToolbox functions.

这将创建完整的TSqlToolbox数据库，其表，表数据行，函数以及主键/外键约束。 在上面的屏幕截图中，FRANK-PC数据库服务器托管TSqlToolbox。 FRANK-PC数据库服务器托管的每个数据库都可以使用TSqlToolbox函数。

引擎盖下 (Under the Hood)

The T-SQL Toolbox database uses data in the following tables for its calculations.

T-SQL工具箱数据库使用下表中的数据进行计算。

1. DateTimeUtil.Timezone
   DateTimeUtil.Timezone
2. DateTimeUtil.TimezoneAdjustmentRule
   DateTimeUtil.TimezoneAdjustmentRule

The Timezone table has one row for each time zone defined in the database.

时区表在数据库中定义的每个时区都有一行。

CREATE TABLE [DateTimeUtil].[Timezone](
  [Id]            [int] NOT NULL,
  [Identifier]        [nvarchar](100) NULL,
  [StandardName]        [nvarchar](100) NULL,
  [DisplayName]       [nvarchar](100) NULL,
  [DaylightName]        [nvarchar](100) NULL,
  [SupportsDaylightSavingTime]  [bit] NULL,
  [BaseUtcOffsetSec]      [int] NULL
)

The .Net TimeZoneInfo class can serve as the primary data source for this table because, as seen here at the Microsoft documentation, this class has the following properties:

在.NET 的TimeZoneInfo类可以作为此表的主要数据来源，因为，如看到这里在微软的文档，这个类具有以下特性：

• Id
  ID
• StandardName
  标准名称
• DisplayName
  显示名称
• DaylightName
  日光名称
• SupportsDaylightSavingTime
  支持日光节约时间
• BaseUtcOffset
  BaseUtcOffset

The T-SQL Toolbox TimezoneAdjustmentRule table has rows that show historical metadata changes for each time zone change, the present defining metadata for each time zone, and planned future changes for specific time zones.

T-SQL工具箱TimezoneAdjustmentRule表中的行显示了每个时区更改的历史元数据更改，当前定义的每个时区的元数据以及计划的特定时区的将来更改。

CREATE TABLE [DateTimeUtil].[TimezoneAdjustmentRule](
  [Id]                [int] NOT NULL,
  [TimezoneId]            [int] NULL,
  [RuleNo]              [int] NULL,
  [DateStart]           [datetime2](7) NULL,
  [DateEnd]             [datetime2](7) NULL,
  [DaylightTransitionStartIsFixedDateRule]  [bit] NULL,
  [DaylightTransitionStartMonth]      [int] NULL,
  [DaylightTransitionStartDay]      [int] NULL,
  [DaylightTransitionStartWeek]     [int] NULL,
  [DaylightTransitionStartDayOfWeek]    [int] NULL,
  [DaylightTransitionStartTimeOfDay]    [time](7) NULL,
  [DaylightTransitionEndIsFixedDateRule]    [bit] NULL,
  [DaylightTransitionEndMonth]      [int] NULL,
  [DaylightTransitionEndDay]        [int] NULL,
  [DaylightTransitionEndWeek]       [int] NULL,
  [DaylightTransitionEndDayOfWeek]      [int] NULL,
  [DaylightTransitionEndTimeOfDay]      [time](7) NULL,
  [DaylightDeltaSec]          [int] NULL
)

The .Net TimeZoneInfo.AdjustmentRule class can serve as the primary data source for this table because, as seen here at the Microsoft documentation, its properties and methods mirror many of the columns of this table. We can clearly see this when we research the DateEnd and DaylightTransitionStart properties of this class, for example.

在.NET TimeZoneInfo.AdjustmentRule类可以作为这个表，因为所观察到的主要数据来源在这里在微软的文档，它的属性和方法反映许多该表的列。 例如，当我们研究此类的DateEnd和DaylightTransitionStart属性时，我们可以清楚地看到这一点。

使用T-SQL工具箱 (Using T-SQL Toolbox)

We’ll use a custom database to work with TSqlToolbox. Run this script in a SQL Server query window:

我们将使用一个自定义数据库来使用TSqlToolbox。 在SQL Server查询窗口中运行以下脚本：

USE [master]
GO
 
--  Use
--
--    xp_create_subdir
--
--  to build a directory for the MDF and LDF
--  files of this database, if necessary.
 
EXEC master.dbo.xp_create_subdir 'C:\TSQL_TOOLBOX_DEMO'
 
CREATE DATABASE [TSQL_TOOLBOX_DEMO]
 ON  PRIMARY 
( NAME = N'TSQL_TOOLBOX_DEMO',
  FILENAME = N'C:\TSQL_TOOLBOX_DEMO\TSQL_TOOLBOX_DEMO.mdf' ,
  SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON
( NAME = N'TSQL_TOOLBOX_DEMO_log',
  FILENAME = N'C:\TSQL_TOOLBOX_DEMO\TSQL_TOOLBOX_DEMO_log.ldf' ,
  SIZE = 1024KB , MAXSIZE = 1GB , FILEGROWTH = 10%)
GO
 
USE [TSQL_TOOLBOX_DEMO]
GO
 
CREATE PROCEDURE [dbo].[CONVERT_GETDATE_VALUE_FROM_ONE_TIMEZONE_TO_UTC]
 
  @timezoneID INT -- an integer mapped to a specific time zone; see the
        -- Tsqltoolbox.DateTimeUtil.Timezone table for all
        -- time zones
 
  --  To test: CONVERT_GETDATE_VALUE_FROM_ONE_TIMEZONE_TO_UTC 6;
  
  --  parameter value 6 maps to 'Pacific Standard Time' in the
  --
  --    TSqlToolbox.DateTimeUtil.Timezone
  --
  --  table
 
AS
 
  DECLARE @localTimeZone as NVARCHAR(50);
 
  SET   @localTimeZone = (
            SELECT  identifier
            FROM    TSqlToolbox.DateTimeUtil.Timezone
            WHERE   Id = @timezoneID
          );
 
  SELECT  @localTimeZone AS 'LOCAL_TIME_ZONE_IDENTIFIER',
      GETDATE() AS 'LOCAL_GETDATE()_VALUE',
      TSqlToolbox.DateTimeUtil.UDF_ConvertLocalToUtcByTimezoneId
        (@timezoneID, GETDATE())
      AS 'UTC_GETDATE()_VALUE_EQUIVALENT';
 
GO

If necessary, it first creates a subdirectory, \TSQL_TOOLBOX_DEMO on the root, and then creates a new database called TSQL_TOOLBOX_DEMO. It places the component LDF and MDF files of the T-SQL Toolbox database in the TSQL_TOOLBOX_DEMO directory. Lastly, it creates the CONVERT_GETDATE_VALUE_FROM_ONE_TIMEZONE_TO_UTC stored procedure, as shown.

如有必要，它将首先在根目录上创建一个子目录\ TSQL_TOOLBOX_DEMO ，然后创建一个名为TSQL_TOOLBOX_DEMO的新数据库。 它将T-SQL工具箱数据库的组件LDF和MDF文件放置在TSQL_TOOLBOX_DEMO目录中。 最后，它创建了CONVERT_GETDATE_VALUE_FROM_ONE_TIMEZONE_TO_UTC存储过程，如图所示。

In the stored procedure SELECT statement, the third expression on lines 25 and 26 calls the TSqlToolbox DateTimeUtil.UDF_ConvertLocalToUtcByTimezoneId function. Like many other TSqlToolbox functions, this function accounts for daylight savings time through calls to the TSqlToolbox.DateTimeUtil.TimezoneAdjustmentRule table.

在存储过程SELECT语句中，第25行和第26行的第三个表达式调用TSqlToolbox DateTimeUtil.UDF_ConvertLocalToUtcByTimezoneId函数。 与其他许多TSqlToolbox函数一样，此函数通过调用TSqlToolbox.DateTimeUtil.TimezoneAdjustmentRule表来解决夏时制时间。

TSqlToolbox.DateTimeUtil.UDF_ConvertLocalToUtcByTimezoneId
    (@timezoneID, GETDATE())

The call at lines 25 and 26 places the TSqlToolbox database name in front of the complete function name. Then it adds the @timezoneID and GETDATE() parameters. Note that the functions have two-part names. “DateTimeUtil” serves as the prefix of all TSqlToolbox function names, and a dot separates the second identifying function name. If a TSqlToolbox function name did not include the “DateTimeUtil” prefix, we would need to substitute the name of the TSqlToolbox database default schema. We usually use dbo for this; “dbo” stands for “database owner.” However, different database environments might handle this naming differently, so check with a database administrator for exact details.

第25和26行的调用将TSqlToolbox数据库名称放在完整函数名称的前面。 然后，它添加@timezoneID和GETDATE（）参数。 请注意，函数具有两部分名称。 “ DateTimeUtil”用作所有TSqlToolbox函数名称的前缀，并且点分隔第二个标识函数名称。 如果TSqlToolbox函数名称不包含“ DateTimeUtil”前缀，我们将需要替换TSqlToolbox数据库默认架构的名称。 我们通常为此使用dbo; “ dbo”代表“数据库所有者”。 但是，不同的数据库环境可能以不同的方式处理此命名，因此请与数据库管理员联系以获取确切的详细信息。

At line 5, the demo stored procedure has test code for the stored procedure itself. The test code returns this row:

在第5行，演示存储过程具有存储过程本身的测试代码。 测试代码返回以下行：

At line 17, it maps the @timezoneID parameter to the time zone name. The Line 23 SELECT statement builds the result set of the finished stored procedure. This shows that with a call to a TSqlToolbox function, we can easily map a local date-time value to a GMT value. In this case, we tried a call to GETDATE(). TSqlToolbox accounts for any local daylight savings time effects.

在第17行，它将@timezoneID参数映射到时区名称。 第23行的SELECT语句构建完成的存储过程的结果集。 这表明通过调用TSqlToolbox函数，我们可以轻松地将本地日期时间值映射为GMT值。 在这种情况下，我们尝试调用GETDATE（）。 TSqlToolbox考虑了任何本地夏时制的影响。

When we look at the T-SQL Toolbox database functions, we can see that they both lever native SQL Server functions and other TSqlToolbox functions. For example, the UDF_GetStartOfDay function first converts the @ReferenceDate parameter – originally a DATETIME2 data type – to a DATE data type. This strips the time data from the @ReferenceDate value. Then, it converts this intermediate value back to a DATETIME2 data type. This conversion restored the time data but zeroed out the time to Start Of Day (SOD).

当我们查看T-SQL Toolbox数据库函数时，我们可以看到它们都利用了本地SQL Server函数和其他TSqlToolbox函数。 例如，UDF_GetStartOfDay函数首先将@ReferenceDate参数（最初为DATETIME2数据类型）转换为DATE数据类型。 这会从@ReferenceDate值中剥离时间数据。 然后，它将中间值转换回DATETIME2数据类型。 此转换恢复了时间数据，但将时间清零至Start Of Day（SOD）。

The next screenshot shows the equivalent conversions.

下一个屏幕截图显示了等效的转换。

As seen below, the TSqlToolbox UDF_ConvertLocalToLocalByTimezoneIdentifier function calls the TSqlToolbox: UDF_ConvertUtcToLocalByTimezoneIdentifier and UDF_ConvertLocalToUtcByTimezoneIdentifier functions to convert a DATETIME2 parameter from one time zone to another. The first two parameters have values from the column [TSqlToolbox].[DateTimeUtil].[Timezone].Identifier.

如下所示，TSqlToolbox UDF_ConvertLocalToLocalByTimezoneIdentifier函数调用TSqlToolbox：UDF_ConvertUtcToLocalByTimezoneIdentifier和UDF_ConvertLocalToUtcByTimezoneIdentifier函数将DATETIME2参数从一个时区转换为另一个时区。 前两个参数具有[TSqlToolbox]。[DateTimeUtil]。[Timezone] .Identifier列中的值。

扩展TSqlToolbox (Extend TSqlToolbox)

As explained earlier, the T-SQL Toolbox database offers functions to calculate start or end day, week, etc. DateTime values for a given DATETIME2 value. We can easily extend these functions. For example, I built and added the UDF_GetEndOfDecade function, seen here, to my local TSqlToolbox database. It calculates the end of decade DATETIME2 value for a given DATETIME2 parameter.

如前所述，T-SQL工具箱数据库提供了用于计算开始或结束日期，星期等的函数。给定DATETIME2值的DateTime值。 我们可以轻松扩展这些功能。 例如，我构建了UDF_GetEndOfDecade函数并将其添加到本地TSqlToolbox数据库中（如此处所示）。 它为给定的DATETIME2参数计算十进制的DATETIME2值。

CREATE FUNCTION DateTimeUtil.UDF_GetEndOfDecade
(
    @ReferenceDate DATETIME2
)
 
RETURNS DATETIME2
 
AS
 
BEGIN
 
  --  GEOY = "Get End Of Year"
 
  DECLARE @GEOY DATETIME2 = NULL
  DECLARE @GEOYYear INT
  DECLARE @GEOYMonth INT
  DECLARE @GEOYDay INT
  DECLARE @YearOffset INT
  DECLARE @Result DATETIME2 = NULL
 
  --  Samples:
 
  --  SELECT TSqlToolbox.DateTimeUtil.UDF_GetEndOfDecade(GETDATE())
  --  SELECT TSqlToolbox.DateTimeUtil.UDF_GetEndOfDecade('3023-05-14 12:45:13')
 
  --  Calculate the EOY value for the input parameter.
 
  SET @GEOY = [TSqlToolbox].[DateTimeUtil].UDF_GetEndOfYear (@ReferenceDate);
 
  --  Parse the EOY value into separate MM / DD / YY values.
 
  SET @GEOYYear = DATEPART(year, @GEOY);
  SET @GEOYMonth = DATEPART(month, @GEOY);
  SET @GEOYDay = DATEPART(day, @GEOY);
 
  --  The % (mod function) calculates the number of years
  --  remaining in the decade. Subtract this value from
  --  10 as the number of years in a decade, and then
  --  subtract 1 to end the decade in year 99 as shown
  --  in this
  --
  --    XX99-12-31 23:59:9999999
  --
  --  format.
 
  SET @YearOffset = (10 - (@GEOYYear % 10) - 1)
 
  --  Build a DateTime value with DATEFROMPARTS(), and
  --  then call UDF_GetEndOfDay.
 
  SELECT @Result =
    [TSqlToolbox].[DateTimeUtil].UDF_GetEndOfDay (DATEFROMPARTS((@GEOYYear + @YearOffset), @GEOYMonth, @GEOYDay));
 
  RETURN @Result;
 
END

This function first calculates the end-of-year (EOY) value for the input parameter. Then, it parses this EOY value into a separate month, day, and year values. The @YearOffset variable calculation uses the % (mod) function to calculate the number of years remaining in the decade of the parameter value year. Finally, the statement first assembles a finished date value from all of the component values, and then uses the UDF_GetEndOfDay function to convert this value to a DATETIME2 return value.

此函数首先计算输入参数的年终（EOY）值。 然后，它将此EOY值解析为单独的月，日和年值。 @YearOffset变量计算使用％（mod）函数来计算参数值年的十年中剩余的年数。 最后，该语句首先从所有组件值中组合一个完成的日期值，然后使用UDF_GetEndOfDay函数将该值转换为DATETIME2返回值。

更新内部T-SQL工具箱数据 (Update Internal T-SQL Toolbox Data)

The TSqlToolbox database functions rely on the data in its Timezone and TimezoneAdjustmentRule tables for their calculations. Obviously, this data does not change in the tables themselves. Matt Johnson‑Pint of Microsoft focuses on Windows time zone engineering. At his blog, he discussed how time zones constantly change. In this Stack Overflow thread, he explained that Windows stores time zone information in the registry. He gave more details about how this works in this Stack Overflow thread. Although the T-SQL Toolbox database has huge value, we need to remember that its core data can become obsolete without updates. The Microsoft Windows update process updates the registry time zone information, so the registry of a fully updated device should have the latest available time zone information. I built this C-Sharp Windows desktop application to query the registry and return the data from the .Net TimeZoneInfo and TimeZoneInfo.AdjustmentRule classes. These classes query the registry time zone values, which we can use to update T-SQL Toolbox. The application uses a pipe symbol “|” to delimit the column values in the rows. I also built this VB.net version, which works the same way. I hosted the C-Sharp and VB.net versions on my GitHub page. They both include complete Visual Studio 2015 solutions and finished executable files.

TSqlToolbox数据库函数的计算依赖于其Timezone和TimezoneAdjustmentRule表中的数据。 显然，这些数据在表本身中不会改变。 Microsoft的Matt Johnson-Pint专注于Windows时区工程。 他在博客上 讨论了时区如何不断变化。 在这个Stack Overflow线程中 ，他解释说Windows将时区信息存储在注册表中。 他提供了有关此堆栈溢出线程中如何工作的更多详细信息。 尽管T-SQL Toolbox数据库具有巨大的价值，但我们需要记住，它的核心数据在没有更新的情况下会过时。 Microsoft Windows更新过程会更新注册表时区信息，因此完全更新的设备的注册表应具有最新的可用时区信息。 我构建了此C-Sharp Windows桌面应用程序，以查询注册表并从.Net TimeZoneInfo和TimeZoneInfo.AdjustmentRule类返回数据。 这些类查询注册表时区值，我们可以使用它们来更新T-SQL Toolbox。 该应用程序使用管道符号“ |” 分隔行中的列值。 我还内置牛逼 他VB.net版本 ，它的工作方式相同。 我在GitHub页面上托管了C-Sharp和VB.net版本。 它们都包含完整的Visual Studio 2015解决方案和完成的可执行文件。

To build SQL Server tables with the application output data, first, update Windows on the development machine. This will apply any time zone updates to the registry. Then run the application and copy the rows from the form text boxes. Use Ctrl-C to copy because the form uses rich text boxes. Place the rows in new text files. Truncate the TSqlToolbox database tables you will update so that they will have completely fresh data. Use the SQL Server Import and Export Wizard to import the values into the T-SQL Toolbox database tables. If this wizard fails because of problems with the data, reformat the data for a SQL Server INSERT statement, and proceed with the INSERT statements in a query window. To build the parent/child table relationships, add integer-value ID columns to the tables as primary and foreign keys.

要使用应用程序输出数据构建S​​QL Server表，首先，在开发计算机上更新Windows 。 这会将所有时区更新应用于注册表。 然后运行该应用程序，并从表单文本框中复制行。 使用Ctrl-C进行复制，因为表单使用富文本框。 将行放在新的文本文件中。 截断您将更新的TSqlToolbox数据库表，以便它们将具有完全新鲜的数据。 使用SQL Server导入和导出向导将值导入到T-SQL Toolbox数据库表中。 如果此向导由于数据问题而失败，请重新格式化SQL Server INSERT语句的数据，然后在查询窗口中继续执行INSERT语句。 要建立父/子表关系，请将整数值ID列作为主键和外键添加到表中。

结论 (Conclusion)

As we saw, T-SQL Toolbox solves tricky problems and returns a huge value. Even better, we can easily extend it and update its data to cover an expanding range of business rules and requirements.

如我们所见，T-SQL工具箱解决了棘手的问题并返回了巨大的价值。 更好的是，我们可以轻松地对其进行扩展和更新其数据，以涵盖不断扩展的业务规则和要求。

翻译自: https://www.sqlshack.com/solve-time-zone-gmt-and-utc-problems-using-the-t-sql-toolbox-database/

pls-toolbox