Time Zone Difference
-
As we know, DST is fast coming up on us. March 11 2007 to be percise. I understand Microsoft has a patch for this event. Which is great and does not solve my issue. What I have is a holding table called StateDST that contains the name of the States and the time zone difference, the time zone difference starting DST (Spring) and end DST(Fall) (eg. South Dakota -5,-4,-5). I have another table called DSTDates that contains the DST dates for the next 15 years (EG. 03/07/2007 02:01 AM).
This is where it gets a little muddled. I have a perment table called States which contains the names of the States and its timezone difference (Eg. South Dakota, -5). What needs to happens is when the DST starts on 03/07/2007 I need to update all the timezone difference column in the State table with the values found in the TimeDifferenceSpring column found in the table StateDST.
I am thinking about running a job that check GetDate() against the entries in the DSTDates table. This job would run daily at 2:15am. If they do not match then do nothing but if they do match I need to implement the code to update the States table (timedifference being the column to be updated for all entries in the tables (this would be all 50 states). Any ideas on how to run this update statement? Or better ideas for implementations?
Thanks
Art
-
My suggestion is to consider a different implementation. I work for a company that deals heavily in airline schedule data. Needless to say, I am frequently concerned with locations and their various time zones. Airlines schedules being what they are, searches can be done for today's date or at any in the future. Thus, I needed a flexible solution that would allow me to search based on that (though depending on your usage of the GMT offsets, this may be a little complex for your needs).
Let's say you have the following STATES table:
[ stateCode | stateName | cntryCode | timezone]
GA | Georgia | US | 1
IN | Indiana | US | 1A
CA | California |US | 4
As you know, the US has 9 different time zones from east to west (1, 1A, 2, 3, 3A, 4, 5, 6, and 6A). These are timezone designators and never change, no matter the date. This is what the timezone column above represents.
Also, I include country, because places like Canada and Australia also have state like areas (provinces..whatever).
Let's also say you have the following TIMEZONES table:
[cntryCode | timezone | effectiveDate | discontinueDate | GMT_offset]
US | 1 | 10/29/2006 2:00am | 3/11/2007 1:59am | -5
US | 1 | 3/11/2007 2:00am | 11/4/2007 1:59am | -4
US | 1A | 1/1/1900 12:00am | 12/31/2999 11:59pm | -5 (Indiana doesn't have daylight savings time)
US | 4 | 10/29/2006 2:00am | 3/11/2007 1:59am | -8
US | 4 | 3/11/2007 2:00am | 11/4/2007 1:59am | -7
I simply join the tables based on the country and timezone, filter on a given time and you have your GMT offset without ever having to update a table.
select s.stateCode, s.statename, s.cntryCode, s.timezone, t.GMT_offset
from states s
join timezones t on s.cntryCode = t.cntryCode and s.timezone = t.timezone
where getdate() between t.effectiveDate and t.discontinueDate
Implementing it in this manner means you only ever have to keep up with the timezone schedules and rarely have to change your queries/procs.
However, if you still need the data in the states table for some reason, you could use your original suggestion and update whatever given columns you need. Implementing it as I have stated above is infinitely more valuable when dealing with complex schedules (especially since changes for timezones can happen when a plane is in the air)
-
Hi Art,
Assuming that the OS is updated with the current Time Zone patches I would take a more proactive approach.
See: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=265378#bm266370
You might also try: http://www.sqlservercentral.com/forums/search.aspx?fid=8&query=daylight%20saving%20time
...if the above link doesn't work as is select "All Forums" in the "Which Forums" list.
The registry contains Time Zone information from all TZ's including the Daylight Saving Time offsets from UTC which can be found in the registry: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones\.
That data in combination with the xp_regread will get you start and end dates for Daylight Savings, assuming it's observed, offsets from UTC, the names of the Time Zones, etc.. Be aware that in certain states part of the state may observe Daylight Saving and another may not.
If you REALLY want a pounding headache, I suggest a trip to:
http://www.timeanddate.com/library/abbreviations/timezones/
There you can see all of the wonderful things people have done to mess with your sidereal cycle.
If you want to add bleeding eyes to the pounding headache, try: http://www.twinsun.com/tz/tz-link.htm
…and don’t forget to visit the FTP site for the data! Be advised that parsing those files into a usable table is not a trivial matter!
Now that you know how to fish…
I agree with Greg’s implementation but would alter the GMT_Offset column to contain minutes instead of hours. Greg’s schedules must not include flights to Newfoundland, Canada! 😉 One other note; my information indicates that parts of Indiana do observe DST and worse, part of it is in the Eastern Time Zone, the rest is in Central… That may throw a wrench in the States table but there is NO EASY WAY to deal with time zones in a precise fashion.
HTH,
Art
-
That is a good point that requires some clarification. Our schedules do indeed cover flights from anywhere to everywhere. So I do need to take minutes into account. My implementation actually has that field as a varchar(5), for example '-0330' or '+0500', however since he was specifically dealing with US data, I was trying to spare him the details since he specifically asked about US locations. Don't even get me started on timezone changes that happen while you're in the air (depending on which direction you are flying of course!)
Technically, states like Indiana, Arizona, and Hawaii however are a little different (hence the timezone codes I mentioned 1A, and 3A, and 6A). Indiana mostly exists in 1A which basically means that it is in the Eastern zone, but doesn't participate in daylight savings time. The western corners of Indiana are part of timezone 2, which is Central. Hawaii and Arizona don't participate in daylight savings at all. Personally, I would put the timezone information in a cities table (or by airport and train station in my applications) due to that fact so that you have the most acurate timezone data no matter what level of "region" you are searching on.
That being said, if you want to stick with just a STATES and TIMEZONES table, then my original suggestion could cover your needs. Actually what either of us is suggesing could just be confusing the daylights (pun intended) out of you, so please feel free to ask either of us for clarification if you need it
- but there is NO EASY WAY to deal with time zones in a precise fashion
I may be oversimplyfying the problem, but would it not make sense that, if time zone issues are a problem in your data or your application, would you not store the time as UTC time? Then the display of the time is a presentation issue, not a data issue.
Just one dummies take on the issue..
Mark
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply