次の方法で共有

Using time zone data in SQL Server 2008

  • [アーティクル]

In SQL Server 2008 Microsoft has introduced a number of new date and time data types. One of these is the datetimeoffset data type. This data type includes an offset from UTC time as well as the datetime value and ensures that the datetime can be retrieved in UTC or a particular timezone based on this offset. There are also new functions to allow for conversions between different time zones using the new function SWITCHOFFSET().

An example from SQL Server 2008 Books On Line (BOL):

CREATE TABLE dbo.test

(

ColDatetimeoffset datetimeoffset

);

GO

INSERT INTO dbo.test

VALUES ('1998-09-20 7:45:50.71345 -5:00');

GO

SELECT SWITCHOFFSET (ColDatetimeoffset, '-08:00')

FROM dbo.test;

GO

--Returns: 1998-09-20 04:45:50.7134500 -08:00

SELECT ColDatetimeoffset

FROM dbo.test;

--Returns: 1998-09-20 07:45:50.7134500 -05:00

One of the most common questions we are asked is why we use the offset and not a timezone name. A timezone name is much easier to remember than an offset, and most people do not know an offset without looking it up, making queries more difficult.

Unfortunately, there is no current international standard authority for timezone names and values. Each system needs to use a system of their own choosing, and until there is an international standard, it is not feasible to try to have SQL Server provide one, and would ultimately cause more problems than it would solve. However, there are a couple of common systems that are well recognized around the world. One is the Dynamic timezone data that is stored in the Windows Vista registry. This data can be read from the registry into a file, which is then imported into SQL Server.

Another cross-platform standard is the public domain Olson Timezone database (https://www.twinsun.com/tz/tz-link.htm). There are many public domain programs for extracting a time zone from these files, but at this time most are programmatic solutions. So a programmatic function could be written in the CLR, but to provide full database functionality and query-ability, a table is needed.

The programmatic solutions take a date, then apply the many different rules that determine when a zone is in daylight savings time and when it is not. However, there are also historical changes. Daylight savings time changed in 2007, meaning that determining what the offset for a particular zone is at a particular time is different depending on the year. Then there are times when leap seconds need to be added. Therefore any data-driven solution must have rows that have valid time ranges as well.

The approach recommended here is to take one of the DLLs found on the web and instead of providing a programmatic solution around a specific date – to write all of the rows out into a database as an offset with the valid ranges. Currently this example uses the Windows standard naming conventions for timezones, with a mapping to the Olson timezone names, but you could easily add other names in other languages as well.

Working with timezones is very complex, and the following is a suggestion only for some ideas on how to use time zone data more effectively. This is an example program (no guarantees) that uses a .NET library and writes the data from the Olson tz files in table format, and which can then be imported into SQL Server. The .NET timezone library can be found at https://www.babiej.demon.nl/Tz4Net/main.htm and they request a small donation.

Here is some sample code to write to files the timezone data (no guarantees – does not include leap seconds):

StreamWriter sr = File.CreateText(@"D:\TZMapping.txt");

StreamWriter tr = File.CreateText(@"D:\TZZones.txt");

string[] zoneNames = OlsonTimeZone.AllNames;

sr.WriteLine("ID\tDaylightName\tStandardName\tRawUtcOffset\tOffsetSeconds\tWin32Id");

tr.WriteLine("ID\tTransitionStart\tTransitionEnd\tDeltaSeconds\tDST");

for (int i = 0; i < zoneNames.Length; i++)

{

OlsonTimeZone tz = OlsonTimeZone.GetInstanceFromOlsonName(zoneNames[i].ToString());

sr.Write(i.ToString() + "\t");

sr.Write(tz.DaylightName.Trim() + "\t");

sr.Write(tz.StandardName.Trim() + "\t");

sr.Write(tz.RawUtcOffset.ToString() + "\t");

sr.Write(tz.RawUtcOffset.TotalSeconds.ToString() + "\t");

sr.WriteLine(tz.Win32Id == null ? "" : tz.Win32Id.Trim());

DaylightTime[] times = tz.AllTimeChanges;

for (int j = 0; j < times.Length; j++)

{

tr.Write(i.ToString() + "\t");

tr.Write(times[j].Start.ToString("yyyy-MM-dd HH:mm:ss") + "\t");

tr.Write(times[j].End.ToString("yyyy-MM-dd HH:mm:ss") + "\t");

tr.Write(times[j] is StandardTime ? "0\t" :times[j].Delta.TotalSeconds.ToString() + "\t");

tr.WriteLine(times[j] is StandardTime ? false.ToString() : true.ToString() );

}

}

tr.WriteLine();

sr.WriteLine();

tr.Close();

sr.Close();

Import the TZMapping file, which will become the parent table, with the ID as the primary key. Your table structure might look like this:

Please note: If you use the Flat File Datasource in the Import Data Wizard in SQL Server 2008 Management Studio, you will need to open the Advanced Tab to set the source OutPutColumnWidth to greater than the default of 50. Then import the TZZones file, which will become the child table with the ID, TransitionStart, and TransitionEnd as the composite primary key with a foreign key reference to the TZMapping table. The TZZones table includes historical timezone data. Joining these new tables into your data into queries now allows for queries that include standard names, Windows IDs, etc.

For example, offsets can now be retrieved by a preferred name:

select UtcOffset from TZmapping where StandardName = 'US/Pacific (PST)'

The following two queries return different offset amounts for the same day in two different years. This is because the US changed daylight savings time, and the date in March now falls into daylight savings when it did not before.

select (dbo.TZMapping.OffsetSeconds + dbo.TZZones.DeltaSeconds)/3600

from dbo.TZMapping

join dbo.TZZones

on dbo.TZMapping.id = dbo.TZZones.id

where dbo.TZMapping.StandardName = 'America/Los_Angeles (PST)'

and '2006-03-15'

between dbo.TZZones.TransitionStart

and dbo.TZZones.TransitionEnd

select (dbo.TZMapping.OffsetSeconds + dbo.TZZones.DeltaSeconds)/3600

from dbo.TZMapping

join dbo.TZZones

on dbo.TZMapping.id = dbo.TZZones.id

where dbo.TZMapping.StandardName = 'America/Los_Angeles (PST)'

and '2007-03-15'

between dbo.TZZones.TransitionStart

and dbo.TZZones.TransitionEnd

Again, timezones are a complex area and each application will need to address how you are going to handle time zone data to make programs more user friendly. This is just one small example.

Comments