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 (
http://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 http://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.

Cross Posted from http://blogs.microsoft.com/mssqlisv


Comments (6)

  1. MichaelGG says:

    I’m still confused, ever since the DateTimeOffset was announced, what problem this actually solves.

    If you want to store a "point in time", use UTC like you probably should already be doing. If you want to store a "timezone aware" structure, then you really need to store the timezone, as you mention.

    And if you store the timezone, then the offset is irrelevant anyways since you can calculate it on the fly.

    DateTimeOffset seems to be solving the problem I’ve never seen of needing to simultaneously store a "local" time with no zone info and UTC. Since you can’t really display this data after saving it (what would you call it? "Local?"), I don’t get the point.

    What should have been included in Windows and .NET a long time ago is a full TimeZone API. Not TZ, but a really strong system with logical layout.

    I don’t like the "Area/City" approach because, AFAIK, it doesn’t address timezones directly. Suppose I save "America/New_York" for my data I want to be in Eastern Time. Then NY decides to opt-out of DST. Now what? All my data is incorrectly coded if I didn’t really mean NY but meant ET. If they at least had a way to do "Etc/EasternTime" or similar, that’d be a much more acceptable solution.

    On top of that, I don’t see what cities buy. Cities are not the logical unit of time selection; they’re a close approximation. When a major time change event occurs, it’s not obvious that the correct decision is just to go with whatever’s stored. It may very well require going and updating time zones for some dates. In the example of NY changing, I’d have to go determine if each new time is actually NY, or Eastern (or something else) and update it. So using NY in the first place bought nothing.

  2. mssqlisv says:

    As you have pointed out, determining how to deal with timezone is not very straight forward.  It is just the issues that you mention, and the fact that there really is not yet a global standard for timezone naming that led us to use the offset.  That allows us to do the calculations needed, without having to be in the business of keeping and updating timezone tables.  Our customers have made it clear that business data is in their domain, not in ours.  We think that is our best approach.

    But you are right in that DateTimeOffset allows you to store a time as UTC and know the offset, but not the offset name.  Does the +8:00 offset refer to an Asian timezone, or an Australian or Antarctica?

    The same problem exists with country names and currency names.  They are different in different languages, but there are now international standards as to the code used to refer to them.  Eventually something will surface as the standard in time as well.

    So this seemed to be the best approach that we could take that would allow for an offset to be known (which is the most important piece of information really) until there is some kind of international standard.

    Thanks for the question!

  3. mssqlisv says:

    In addition, the SQLDEV team would like to clarify the following reasons for us defining the new DATETIMEOFFSET:

    1. SQL Standard compliance – It’s aligned with the SQL99 standard type ‘Timestamp with time zone’ and our standard compliant consistency.

    2. Easy & seamless migration/replication – It provides a data-safe migration/replication solution between SQL Server and Oracle that also supports the SQL standard ‘Timestamp with time zone’ type 3. Data accuracy and precise reporting for globalized database application – For any across region/country database application, the datetime value becomes ambiguous without the time zone offset. ‘Date + time + time zone offset’ all together should be the real representation of the actual data source. Time zone offset is the standard (in comparing with time zone name as the first post pointed out) and building block for time zone aware applications. In addition, there are indeed real world scenarios of storing & retrieving datetime value together with the original time zone offset. (i.e. the purchase reporting for on-line shopping) 4. Align with industry standard – Both XML and web service supports the datetime value with ‘time zone offset’ enclosed. And we need to provide the compliant storage data type for data-safe data transformation and integration.

  4. MichaelGG says:

    Yea, you’re right on the standard stuff. That goes back a lot further and SQL supporting it is pretty good. You guys didn’t invent the poor situation, you just have to deal with it :).

    And yes, SQL team is probably not the one who should be going out and doing a global time zone effort. It’d just be nice for some team at MSFT to make an intelligent global tz effort (someone’s gotta save us from the politicians that come up with this stuff).

  5. dotNetProfessional says:

    In SQL 2000 I used to retrieve datq via a FOR XML PATH syntax and ‘decorate’ the dates comming back into the XML Z format (the dates were all stored in UTC). When deserializing (which is how I populate the entities from the XML result), the date value recognised the date was in UTC (ie datetime.Kind). Will we have the same result now with standard selects from this new data type? Ie will .NET recognise the new datatype as being a UTC offset?

  6. It helps in dealing with diffrent zone datetime requirment but universal standard in very much needed.