使用预定义的偏移值进行时区转换 [英] Timezone conversion using pre-defined offset values

问题描述

嗨
我正在搜索提交的文章，并在SQL Server中找到了时区转换的参考:将GMT转换为本地日期时间.

我正在尝试运行有关事件的报告.我必须根据行数据中提供的时区值将UTC事件时间转换为本地时间.上面的文章非常好.但是，我的困境是我的数据结构不同，这意味着我拥有的数据不匹配.

这是我的时区数据的示例:

1(GMT + 04:30)喀布尔270 0 0 0 0 0 0 0 -270 0 -60 0 0 0 0 0 0阿富汗标准时间

可以使用以下脚本创建时区表:

Hi
I was searching through the submitted articles and found a reference for a timezone conversion in SQL Server: Convert GMT to Local Date time.

I am attempting to run reports on events. I must convert a UTC event time to local time based on the timezone value provided in the row data. The article above is excellent. However, my dilemma is that the data I have does not match, in the sense that my data structure is different.

Here is an example of my timezone data:

1 (GMT+04:30) Kabul 270 0 0 0 0 0 0 0 -270 0 -60 0 0 0 0 0 0 Afghanistan Standard Time

The timezone table can be created with the following script:

USE [Demo_Model]
GO
/****** Object:  Table [dbo].[WORLDTZ]    Script Date: 05/12/2010 11:49:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WORLDTZ](
    [WORLDTZID] [int] NOT NULL,
    [DISPLAYNAME] [nvarchar](128) NOT NULL,
    [GMTOFFSET] [int] NOT NULL,
    [DST] [smallint] NOT NULL,
    [STANDARDMONTH] [int] NOT NULL,
    [STANDARDDAY] [int] NOT NULL,
    [STANDARDWEEK] [int] NOT NULL,
    [DAYLIGHTMONTH] [int] NOT NULL,
    [DAYLIGHTDAY] [int] NOT NULL,
    [DAYLIGHTWEEK] [int] NOT NULL,
    [BIAS] [int] NOT NULL,
    [STANDARDBIAS] [int] NOT NULL,
    [DAYLIGHTBIAS] [int] NOT NULL,
    [STANDARDHOUR] [int] NOT NULL,
    [DAYLIGHTHOUR] [int] NOT NULL,
    [STANDARDMINUTE] [smallint] NOT NULL DEFAULT ((0)),
    [STANDARDSECOND] [smallint] NOT NULL DEFAULT ((0)),
    [DAYLIGHTMINUTE] [smallint] NOT NULL DEFAULT ((0)),
    [DAYLIGHTSECOND] [smallint] NOT NULL DEFAULT ((0)),
    [WINDOWS_TZID] [nvarchar](32) NULL,
 CONSTRAINT [WORLDTZ_PK] PRIMARY KEY NONCLUSTERED
(
    [WORLDTZID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

我正在努力使用SQL UTC函数来创建一种方法来读取我的UTC值和WorldTZID以生成本地时间值.

I am struggling with SQL UTC functions to create a method to read in my UTC value and WorldTZID to generate the local time value.

推荐答案

首先，您的数据库表没有不会让夏令时变得容易，因为您将需要多个步骤将UTC/GMT转换为当地时间.
首先创建一些变量来保存GMTOFFSET，DAYLIGHTBIAS，STANDARDMONTH/DAY/HOUR/MINUTE/SECOND和DAYLIGHTMONTH/DAY/HOUR/MINUTE/SECOND
然后，您需要从WORLDTZ表中选择数据，例如;

First up your database table doesn''t make dealing with daylight savings times easy as such you will need multiple steps to to convert UTC/GMT to local time.
Start with creating some variables to hold GMTOFFSET, DAYLIGHTBIAS, STANDARDMONTH/DAY/HOUR/MINUTE/SECOND and DAYLIGHTMONTH/DAY/HOUR/MINUTE/SECOND
You then need to select the data from the WORLDTZ table into those variables like;

SELECT @GMTOFFEST=GMTOFFSET, @DAYLIGHTBIAS=DAYLIGHTBIAS, ...
FROM WORLDTZ
WHERE WORLDTZID= @WORLDTZID

接下来获取本地日期时间；

Next get the local datetime;

SET @LOCALDATETIME = DATEADD(n, @GMTOFFEST, @EVENTDATETIME)

现在，您有一个不考虑夏令时的datetime值.
现在，如果适用，您现在需要调整@LOCALDATETIME变量以实现夏时制.
如果它们完全相同，请查看STANDARDMONTH/DAY/HOUR/MINUTE/SECOND和DAYLIGHTMONTH/DAY/HOUR/MINUTE/SECOND变量，如果它们相同，则无需调整@LOCALDATETIME变量，如果需要确定不同如果您的活动日期时间值在该时区的夏令时之内.

要确定夏令时的开始，您需要使用@EVENTDATETIME变量中的年份，然后将其与DAYLIGHTMONTH/DAY/HOUR/MINUTE/SECOND变量组合以提供夏令时的开始日期时间，即@DAYLIGHTSTART现在您需要确定是否夏令时在同一年开始和结束.

为北半球节省日光

You now have a datetime value that does not take into account daylight savings.
You now need to adjust the @LOCALDATETIME variable for daylight savings if it is applicable.
Look at the STANDARDMONTH/DAY/HOUR/MINUTE/SECOND and DAYLIGHTMONTH/DAY/HOUR/MINUTE/SECOND variables if they are all the same you don''t need to adjust the @LOCALDATETIME variable if that are different you need to determine if your event datetime value falls within the daylight savings time for the timezone.

To determine the daylight savings start you need the year from the @EVENTDATETIME variable and then combine that with the DAYLIGHTMONTH/DAY/HOUR/MINUTE/SECOND vairables to give a start datetime for daylight savings i.e. @DAYLIGHTSTART now you need to determine if daylight savings starts and ends in the same year.

For northern hemisphere daylight savings

if STANDARDMONTH > DAYLIGHTMONTH then
 Combine STANDARDMONTH/DAY/HOUR/MINUTE/SECOND and year from <code>@EVENTDATETIME</code> to give <code>@DAYLIGHTEND</code>
    if @LOCALDATETIME >= @DAYLIGHTSTART AND @LOCALDATETIME <= @DAYLIGHTEND then
        SET @LOCALDATETIME = DATEADD(n, @DAYLIGHTBIAS, @LOCALDATETIME)

为南半球节省夏令时

For southern hemisphere daylight savings

if STANDARDMONTH < DAYLIGHTMONTH then
Combine STANDARDMONTH/DAY/HOUR/MINUTE/SECOND and year from @EVENTDATETIME to give @DAYLIGHTEND
Add 1 year to @DAYLIGHTEND to get @DAYLIGHTENDNEXTYEAR

if @LOCALDATETIME < @DAYLIGHTEND OR (@LOCALDATETIME >= @DAYLIGHTSTART AND @LOCALDATETIME <= @DAYLIGHTENDNEXTYEAR) then
    SET @LOCALDATETIME = DATEADD(n, @DAYLIGHTBIAS, @LOCALDATETIME)

或者，您可以更改WORLDTZ表以更接近您所链接的文章，因为当前表存在的另一个问题是它没有考虑到可能会更改输入休假日的日期的国家/地区由于各种原因.

Or you could change the WORLDTZ table to reflect something closer to the article you linked to as one other problem your current table has is that it doesn''t take into account countries that may change the date they enter end leave daylight savings which does occur for various reasons.

这篇关于使用预定义的偏移值进行时区转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！