January 1, 2009 at 8:08 pm
OK, here's a curly one. How would you work out the amount of time between two dates, taking into account daylight savings? Yes, easy sounding I know... however, the wrinkle is: what happens if the time interval includes a change in daylight savings time for one year? For instance, in February 2007 Melbourne in Australia changed their DST for one year only and Microsoft had to release a cumulative DST patch for it.
So I'm curious as to how people here would approach this.
January 2, 2009 at 5:49 am
The only way to do this is to store the daylight savings information per region in the database.
Then you can write functions to calculate time change.
The Code Project has an article and code to download: http://www.codeproject.com/KB/database/ConvertUTCToLocal.aspx
If I recall correctly, one of the functions had a small bug. I don't remember which one, you'll need to test them.
January 2, 2009 at 9:02 am
For the purist time is time. Why not store things as UTC ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
January 2, 2009 at 9:15 am
I agree that times should be stored as UTC in the database.
However, usually the user wants to see dates/times in their own time zone.
January 2, 2009 at 9:19 am
I also forgot to mention that it is easier & faster to do this in code instead of SQL.
I would recommend doing that if at all possible.
January 2, 2009 at 9:19 am
formatting data and such things like conversion to local time zone settings are generally the responsibility og the front end application - store it in UTC.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
January 2, 2009 at 9:26 am
Yes, it would be ideal to do the conversion in the front end application. But sometimes you do need to do the conversion in the database.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply