Quick Timeline Fix - MySQL Error
Posted on: 6 October 2010
It's taken me far too long to figure this bug out, but I'm pleased to announce I've finally nipped it in the bud. For as long as version 2 of this blog has been in place, the timeline hasn't always worked as it should. To summarise, for those who haven't noticed, clicking on a date in the timeline that should show the blog(s) posted on that day, often came up with "0 entries posted on..." - when clearly the timeline shows otherwise.
Eventually I realised this is due to the timezone compensation I made in the PHP coding across the site. Unfortunately this change wasn't reflected in the MySQL responsible for grabbing the blog entry out of the database. So, often, the actual date the blog post was posted on and the date the MySQL thought it was posted on were conflicting.
To ensure the dates generated by PHP on the site were in GMT (my time), I used the following code:
date_default_timezone_set("Europe/London");
The reason for having to explicitly state the timezone was due to the server being hosted in the US, who are obviously on a different timezone.
To change the timezone in MySQL was a little trickier. The following code will do it:
mysql_query("SET time_zone = '+0:00'");
The value of 'time_zone' determines the GMT offset. By assigning it a value of 0, I am essentially setting it to GMT and overwriting any existing default values that may have been set by the server. This is all fine and dandy, except we're not currently on GMT, we're on BST (British Summer Time), which is GMT +1:00. Great, so I can simply change the value of 'time_zone', above, to '+1:00'. However, I really don't want to have to change this back to +0:00 at the end of the month when the UK returns to GMT, and then keep alternating for eternity; no, there must be a better way. And fortunately there is:
mysql_query("SET time_zone = '". date('P')."'");
The PHP date() function to the rescue. Using the 'P' character in the format string of the date() function returns the difference to GMT of the current timestamp. As I've already set the timezone in PHP to 'Europe/London', it knows that we are in BST. Therefore, date('P') returns +1:00. When the UK changes back to GMT, date('P') will return +0:00. Nifty, eh?