Link to home
Start Free TrialLog in
Avatar of mikha
mikhaFlag for United States of America

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?
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

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.
Avatar of mikha

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
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>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.