mikha
asked on
TimeZone, day light savings, Sql server, asp.net
I trying to find good articles on how to best save timezone information in sql server database. most of the articles are old so here are my questions
1. if i want to save client's timezone, how would i save it in sql server. do i just make another column to save it or is there a better way. I would imagine there should be enumerations for all the timezone in .Net.
2. It looks like it is better to save your datetime in UTC and then do the calculations to convert the data/time to client's timezone. This would require calculating all the data/time fields, every time. Is there a better implementation than this.
Also, how would one keep track of day light savings?
1. if i want to save client's timezone, how would i save it in sql server. do i just make another column to save it or is there a better way. I would imagine there should be enumerations for all the timezone in .Net.
2. It looks like it is better to save your datetime in UTC and then do the calculations to convert the data/time to client's timezone. This would require calculating all the data/time fields, every time. Is there a better implementation than this.
Also, how would one keep track of day light savings?
Since SQL Server 2008 that you can store the time offset with the datetimeoffset data type.
>>most of the articles are old
Probably because there is no point reinventing the wheel. What worked then still works now.
If you need to perform calculations independent of the timezone then converting to UTC makes sense.
Probably because there is no point reinventing the wheel. What worked then still works now.
If you need to perform calculations independent of the timezone then converting to UTC makes sense.
ASKER
thanks @Vitor Montalvão. so, if i also, want client to specify which time zone they are in and save that as a their setting in asp.net and sql server application. which data type/column in sql server, do i use?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>This would require calculating all the data/time fields, every time.
I once had an airline as a client and had to come up with time zone conversions by airport by local datetime, using a 1:M airport-to-timezone table, and including a couple of special 'timezones' for two US states that do not participate in daylight savings time. It was a large amount of code.
With that in mind, if you believe your client will need to frequently account for location when pulling up this date, then storing it as UTC via datetimeoffset would make sense.
I once had an airline as a client and had to come up with time zone conversions by airport by local datetime, using a 1:M airport-to-timezone table, and including a couple of special 'timezones' for two US states that do not participate in daylight savings time. It was a large amount of code.
With that in mind, if you believe your client will need to frequently account for location when pulling up this date, then storing it as UTC via datetimeoffset would make sense.