The Time Zone Challenge

This entry is mostly just me rambling on about technical stuff and thinking out loud. If you want to read something really entertaining, read “Freedom, Justice and a Disturbingly Gaping Ass” instead.

Work continued on Buingo.com today. I’ve decided to lay down the user logic first to make room for more users and hence more content. That one of the goals of Buingo.com is world domination creates an interesting challenge:

How to handle time zone correctly?

The natural thing would be that every user had the times and dates associated with each Buingo converted to his or her local time zone. If a user in Florida (GMT-5) browsed the Buingo calendar of a user in Paris (GMT+1) posted at one in the night on Friday local time, it would appear as posted at 7pm in the evening on Thursday for the Florida user. Some people would probably argue that everything should be in the local times of the posters, but this way seems natural to me.

If this was a meeting calendar you would like to have all meetings scheduled displayed in your local time, wouldn’t you? Yes, you would.

So now I’m saving everything as GMT in the database regardless of the time zone of the poster. I’m also able to convert all the dates to the local time zone of the person browsing the content once the GMT value is extracted from the database, but I can’t get the calendar to display the Buingos correctly. The problem is that the time zones has to be converted in the database as the database query is executed, not just after the GMT value is extracted.

MySQL has a method for this, CONVERT_TZ, but I have not been able to get it to work properly yet and it’s not supported by the MySQL version my host is using.

Another day, another challenge.

In other, related news; the number of subscribers to the mailing list has actually doubled from January 1. That’s a good start. You an find out how to subscribe at Buingo.com.


Feedback

Do you have any thoughts you want to share? A question, maybe? Or is something in this post just plainly wrong? Then please send an e-mail to vegard at vegard dot net with your input. You can also use any of the other points of contact listed on the About page.

Caution

It looks like you're using Google's Chrome browser, which records everything you do on the internet. Personally identifiable and sensitive information about you is then sold to the highest bidder, making you a part of surveillance capitalism.

The Contra Chrome comic explains why this is bad, and why you should use another browser.