Timezones, MySQL and C#
Well I’ve just found myself a problem that’s much harder than it looks. One of the systems I’m working on at the moment needs to support international users in various time zones. At first glance it seemed reasonably easy, ensure all the dates are saved as UTC and then in the display layer add (or subtract) the right number of hours to get the correct localised time. I’d have to take into account daylight saving but that’s not a major problem is it?
Apparently it is. I thought most places change around the same time (for example Europe is pretty much harmonized), they don’t. In fact some countries change they clocks more than twice a year!
So it became clear I’d need to use a library to do it. Unfortunately C# lacks such a library. It does have support for determining the current time zone and getting some information through it (the TimeZone class) but nothing more substantial.
MySQL has a lovely set of tables and functions almost built in to do it. On windows you must download the tz tables and linux generate them. This was good; I could convert datetimes between time zones with no problem. However I didn’t want to be calling the database everytime I wanted a conversion. I tend to aim for no more than 20 database calls a page for performance reasons (and normally much less).
I came up with a two pronged approach: Whenever I get a date from the database I think I might use in business logic (UTC) and display (User time zone) I get two copies, one in UTC and the other in the localised. To make sure there’s no confusion, I always name variables or properties with a name ending in local if they’ve been converted.
Additionally I keep a decimal stored in the session representing the offset (e.g. +1 Hour for BST) for doing any ‘on the fly’ conversions. I update this variable once every page load (just in case we begin daylight saving).
I noticed a few gotchas along the way:
- make sure you set the timezone for the mysql connection, or mysql will do conversions wrong.
- don’t use datetime.now.ToUniversal() time, use datetime.UtcNow (see this article on best practises)
Henry