Friday, October 26, 2007

Solving Tough Problems: Timezones and DST

I spent all my time putting out fires this week, and none of my time adding cool functionality. The trouble really started when I tried solving what I assumed to be common problems. But of course, when I tried to figure out how other people had dealt with these problems, I ran into a brick wall. Well, not a brick wall actually. Instead I found lots of "solutions". All of which took me forever to realize they had nothing to do with my problems.

The disaster actually started out as planned. I was working on some functionality dealing with calendar data (which is a difficult subject). This should be a simple thing. You've got a user in one time zone and another user in a different one? No problem. This isn't exactly a new problem. There should be support for such a thing in all this advanced technology. And it should be very natural to convert between them. And it should be natural to work with both computer oriented times as well as human oriented times. By which I mean the ridiculous practices we have of leap years, leap seconds, timezone offsets, and daylight savings times.

As it turns out, I needed to find different solutions for two operating systems, and three computing platforms (and I'm not even doing much AJAX yet). Just to give you a quick overview, most of the problems arise around counting the number of seconds in a day.

There's 86400 seconds in most days. But things get hectic with leap seconds and more importantly daylight savings time (which adds or subtracts an hour from two days a year). PHP provides the function strtotime which lets you do stuff like strtotime('+1 day') to add a day, taking into account daylight savings time so that today at 6:00am "+1 day" is 6:00am tomorrow. Combine this with date-default-timezone-set and you're ready to go, no matter where your users are.

Python, smug as always [ed: I love python], provides a timedelta class, assuming 86400 seconds in every day. This is as opposed to being timezone aware (even if you install and correctly use pytz). And it gets worse. You can convert from a UTC timestamp using the handy method datetime.fromutctimestamp. But how do you convert back? Is there a datetime.toutctimestamp? No, there isn't. What about the traditional mktime? That's only going to work if the datetime you're working with is in the system's timezone. And don't even try converting your datetime to the system's timezone. The only way to access the system's timezone is time.tzname which is non-standard and incompatible with pytz. I ended up using a combination of calendar.timegm and datetime.utctimetuple. No searches I tried found this solution.

Don't even get me started on MySQL. Check out this article on timezone support. And take a look at this list of date/time functions. But don't try using the timezone db they've got for download. It was inconsistent with all the olson zoneinfo databases I saw. I had to use mysql_tzinfo_to_sql on a Linux system and copy the resulting SQL script to my Windows box and apply it manually (mysql -u root -p mysql < zoneinfo.sql).

That's probably enough geeking out / ranting for this week. Next week I'll tell you about how I solved my very unexpected "(13)Permission Denied" / "403 Unauthorized" / ".htaccess pcfg_openfile: unable to check htaccess file, ensure it is readable" / "No input file specified" errors.

1 comment:

jo'pesche' said...

it sounds like a project i've wanted to do for ages. time zone handling in pims has always annoyed me: the biggest problem is that most pims seem to ignore the fact that people are a) smart and b) invariably locally situated. so if i am in EST, and i say i have a flight to the uk at 7pm followed by a 9am meeting (horrible combination though that may be), then I mean 7pm EST and 9am GMT. and i don't want to have to specify that, and i definitely don't want my bloody pim to assume that i mean 9am EST and then change that meeting to 4am EST when i tell my computer i'm now in GMT. that's the approach that a lot of pims use, and it's a hideous combination of sloppy programming, trying to be smarter than the user, and assuming that your user is stupid. there may be times when it's appropriate for users to have to actively engage with time zone changes in that manner -- notably scheduling conference calls, say, across multiple time zones -- but even in that it seems better to assume smart users as much as possible.

rant over. sorry about that.