Let’s get this out of the way first. I can’t sum up the problems that timezones cause develoeprs any better than this.
Now that we have that out of the way, we can’t really use the libraries he references since we’re C# developers. Sure, the framework has a lot of great tools, but how do we use them exactly?
Lets’s start with this mandate.
ALL DATES AND TIMES IN THE DATABASE ARE UTC!
We want to know that our source data is consistent. When presenting data to users, we’ll adjust the data for display purposes. Anything in the database MUST be stored as UTC.
When it comes to saving the timezone for a user, you *can* just save the offset. Central Standard Time for the United States is -6.0. Notice it’s a float. You can’t just use an integer as some time zones are offset by half an hour or three quarters of an hour.
That being said though, simply saving the offset is probably not the best way to do it. It’s certainly possible, but it’s not optimal. If that is what’s being done, you will need to issue commands like:
to get the date and time adjusted properly.
A much better way to do it is to let the framework work for you. If you get yourself a snazzy TimeZoneInfo object, you can use that to convert to and from UTC quite easily.
TimeZoneInfo tz = TimeZoneInfo.FindSystemTimeZoneById("Central Standard Time");
DateTime localized = TimeZoneInfo.ConvertTimeFromUtc(utcDateTime, tz);
As you can see in that example, the localized date time got converted from UTC using the TimeZoneInfo object. The TimeZoneInfo object was pulled from the list by it’s id. There are 102 timezones to choose from because:
- Some places respect daylight savings
- Other reasons… I don’t know why. How can there be 102 when there are only 24 hours in a day?!
Anyway, determining the TimeZoneInfo object to get based on it’s id is much simpler and more reliable than using the timezone offset. There are four timezones with an offset of -6.0. Do they all respect daylight savings? Who knows? Using the id to specify “Central Standard Time” versus “Canada Central Standard Time” will make it easier.
What I’ve chosen to do is implement code in my business object (BO) layer that will adjust any dates and times for all my objects based on the timezone of the currently logged in user. The flow looks something like this:
- In the constructor of the BO layer, pass the user id of the currently logged in user
- Get the timezone for the currently logged in user
- Make the call to the database to retrieve the data you’re after
- Iterate through the data, adjusting the dates and times
- Return the adjusted data back to the caller
When saving data, you also need to make sure you’re adjusting any dates/times back to UTC. Just like the TimeZoneInfo class has the ConvertFromUtc, it also has a ConvertToUtc.
In addition to ensuring that all dates in the database are UTC and using the UTC strategies above, you must ensure that the timezone of the database server is also set to +00:00. In MySQL, all datetimes are stored as UTC but are displayed in the local timezone. This will screw up the universe. Setting the timezone of the server to +00:00 will help.