| Author |
Topic |
|
Marioi
Posting Yak Master
132 Posts |
 Posted - 2007-07-24 : 17:28:56
|
| I have an ASP.net 2.0 application running off of SS (currently SS2000, but soon to be migrated to 2005). We have users world-wide. We need to accept and display datetimes and allow querying data via our filtering facility based on the user's time zone. This would be fairly straightforward if times in each time zone were always at a certain offset from GMT, but daylight savings time rules vary. The dates when DST kicks in and ends, for example, are not the same in the US and Europe.We want to support users entering a datetime and other users seeing the correct time in his or her time zone. Further, when someone queries the database, we want them to see records with the proper datetime for their time zone. For example, if a US user is scheduling an appointment for October or November, (s)he needs to see the availabilty of European users correctly adjusted for the fact that DST may have ended in Europe and is still in effect in the US or vice versa. Have you done any work in this area or encountered a solution? |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-07-24 : 17:45:26
|
| I have done a lot of work in this area, and the simple answer is that it is just a lot of work.An issue that you did not mention is that the rules for when or if DST starts and stops changes from year to year. It is necessary to keep tables that show the DST start and stop time for each time zone for each year and the UTC offset with and without DST. Windows versions up through 2003 are not aware of this; I think there may be support for this in Vista and beyond.There are many other subtle issues to be aware of. For example, most US and Canadian time zones change at 2:00 am, but at least one Canadian province changes at midnight.One thing I would recommend is that you keep your times in UTC, so that you have a common time standard.CODO ERGO SUM |
 |
|
|
Marioi
Posting Yak Master
132 Posts |
Posted - 2007-07-24 : 17:52:40
|
Queries that involve datetimes, then, must be brutal, especially if they span multiple years?quote:
Originally posted by Michael Valentine Jones
I have done a lot of work in this area, and the simple answer is that it is just a lot of work.An issue that you did not mention is that the rules for when or if DST starts and stops changes from year to year. It is necessary to keep tables that show the DST start and stop time for each time zone for each year and the UTC offset with and without DST. Windows versions up through 2003 are not aware of this; I think there may be support for this in Vista and beyond.There are many other subtle issues to be aware of. For example, most US and Canadian time zones change at 2:00 am, but at least one Canadian province changes at midnight.One thing I would recommend is that you keep your times in UTC, so that you have a common time standard.CODO ERGO SUM
|
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-07-25 : 09:24:49
|
quote:
Originally posted by Marioi
Queries that involve datetimes, then, must be brutal, especially if they span multiple years?quote:
Originally posted by Michael Valentine Jones
I have done a lot of work in this area, and the simple answer is that it is just a lot of work.An issue that you did not mention is that the rules for when or if DST starts and stops changes from year to year. It is necessary to keep tables that show the DST start and stop time for each time zone for each year and the UTC offset with and without DST. Windows versions up through 2003 are not aware of this; I think there may be support for this in Vista and beyond.There are many other subtle issues to be aware of. For example, most US and Canadian time zones change at 2:00 am, but at least one Canadian province changes at midnight.One thing I would recommend is that you keep your times in UTC, so that you have a common time standard.CODO ERGO SUM
I did mention that it's a lot of work.CODO ERGO SUM |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
Marioi
Posting Yak Master
132 Posts |
Posted - 2007-07-25 : 14:11:38
|
quote:
Originally posted by spirit1
maybe this will be of help:http://weblogs.sqlteam.com/mladenp/archive/2006/05/21/9926.aspxjust use users regional settings from javascript
That was helpful. It's interesting that you record the timezone offset in the data. I wonder how you use it.Having the offset in the data is great when you are querying the data from the perspective of that locale's time zone because you can determine for every entry what the local time was at the time of data entry. But when the query is made by a user in another time zone with different DST rules, the query must use the rules of that locale's time zone. If you always write queries based on locale's rules, why would you need the offset in the data? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-07-25 : 14:15:36
|
| well that of course depends on the app and your use.at the time i worked on an app that needed it handled like that.the best way for you would probably be to simply store all dates SQL Server in UTC and then take the users timezone and your UTC date and with little datetime arithmetic you can do whatever you want.maybe you should give us an example query that's giving you trouble._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
Marioi
Posting Yak Master
132 Posts |
Posted - 2007-07-25 : 15:34:12
|
| I am going in that direction. Thanks for your help! |
|
|
|
Marioi
Posting Yak Master
132 Posts |
Posted - 2007-07-26 : 11:49:04
|
quote:
Originally posted by Michael Valentine Jones
It is necessary to keep tables that show the DST start and stop time for each time zone for each year and the UTC offset with and without DST.
Michael, what's your source of DST start/stop times and UTC offsets? Do you use some public domain tables or did you compose and populate your own? Would you mind sharing the schema of those tables?quote:
Windows versions up through 2003 are not aware of this; I think there may be support for this in Vista and beyond.
Are you saying that previous versions of Windows (for example 2000) are not aware of DST start/stop times or time zone offsets in general? Under XP the JS call GetTimezoneOffset() appears to return an offset properly adjusted for DST. I can test this later under Win2k. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-07-26 : 11:58:06
|
quote:
Originally posted by Marioiquote:
Windows versions up through 2003 are not aware of this; I think there may be support for this in Vista and beyond.
Are you saying that previous versions of Windows (for example 2000) are not aware of DST start/stop times or time zone offsets in general? Under XP the JS call GetTimezoneOffset() appears to return an offset properly adjusted for DST. I can test this later under Win2k.
No, what I was pointing out was that versions of Windows before Vista do not support DST settings for prior or future years. The only offset that they are aware of is what is currently defined.For example, the days that DST starts and stops changed in 2007 in the US and Canada. The windows registry only stores the current offset definition, so you cannot use info from the Windows registry to tell you when DST started and stopped in 2005.CODO ERGO SUM |
|
|
|
Marioi
Posting Yak Master
132 Posts |
Posted - 2007-07-26 : 12:04:15
|
quote:
No, what I was pointing out was that versions of Windows before Vista do not support DST settings for prior or future years. The only offset that they are aware of is what is currently defined.
Understood. I am ready to take the table approach, as you suggested. Your source(s)? I am looking at http://www.twinsun.com/tz/tz-link.htm. I read MS materials on global apps, but haven't encountered (yet) a reference to a DB of DST start/end dates. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
|
|
Marioi
Posting Yak Master
132 Posts |
Posted - 2007-07-26 : 12:32:09
|
quote:
Originally posted by Michael Valentine Jones
You may find this link useful:http://en.wikipedia.org/wiki/Tz_database
Looking at it. Thanks! |
|
|
|
Marioi
Posting Yak Master
132 Posts |
Posted - 2007-07-26 : 13:42:55
|
quote:
http://en.wikipedia.org/wiki/Tz_database
I am lost in all this Unix stuff. I downloaded tzarchive.gz, but WinZip is asking me what to name the enclosed file. When I open it in Notepad, I see it has text and code, must be this is some kind of an archive...I looked in txdata2007f.tar.gz. The file named northamerica appears to contain tons of historical data on various areas, but i am not sure how this is supposed to be extracted into a single table of zones and on/off dates. Have any suggestions? I found tziCal. Is that it? I am not familiar with the iCalendar format to which it converts time zone data from the tz database. TIA, |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-07-26 : 13:57:28
|
| iCal is an standardized calendar implementation that you can use across outlook, google cal, yahoo, etc...it saved data in UTC_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
|
Global datetimes, time zones, cultures