Timezones: The client, The server and The database

To Start out, I want to say why I’m writing this post. I have just recently been struggling with server to database interactions with date time and I had no idea why. Thought was “maybe I’m using the wrong time stamp”, “maybe I’m not setting the time to Midnight morning” then i realized “maybe my server is at a different timezone than my database”. And this was very very true.

SQL at base makes their timezones based on the servers timezone. That means if you’re in New York, running a server, it will be based off New York. If London, it will be London. This is ok, so long as your server is on the same timezone, and unfortunately for me, it doesn’t seem like it.  Wordpress has a few functions to work with such as current_time() and the seemingly undocumented option time_zone. Except its useless when it is empty, basically telling me UTC (the universal time for computers generally). PHP also returned my UTC which also doesn’t help me. In all who I hang with the most (My clean cut but simple friend wordpress and my old disorganized but extremely intelligent friend PHP) just don’t see at the same level as mySQL. So what do I do? Well, change mySQLs timezone and change my servers timezone to ensure everyone sees the same.

$wpdb->query("SET time_zone = '+0:00'");
date_default_timezone_set('UTC');

Based off this post and the php manual,  I’ve found this is the ultimate solution to my problem. I was having such problems with timezones being in different places and wordpress not helping too much that I’ve just given up and decided the proper solution is just to set everything in UTC.

This isn’t what I necessarilly want to do though. The reason for this is I’m forcing the user to experience my application in UTC. Why is this a problem? Well, user experience. However, if I’m going to change the website dependent on the users time zone, I would need to find the persons position in the world to find out what time zone they are in. And generally users don’t just give away their time zones. So how am I suppose to do this?

I make the theme have all datetime oriented aspects in my theme return in UTC and when I want to shift the date, either through xslt or jquery, I change the date based on what I recieve from javascript. It isn’t pretty. But I can make it pretty. And functionality is more important than in this case