Recently I've encountered a problem where a date that is during BST (GMT + 1) when stored in MySQL and retrieved using their UNIX_TIMESTAMP function, an hour is lost to make the timestamp UTC. This in many cases is undesirable, especially if you only keep track of the date as this means deducting an hour will make the timestamp become 23:00 on the previous day - or maybe worse depending on what timezone you live in (or at least which timezone your server is set to).

If you've not experienced this yourself, or at least think you haven't, then a good way to test this for yourself is to try out some SQL from a bug report on the MySQL website.

UNIX_TIMESTAMP('2008-04-25')

This, in the case of a server set to GMT, would produce a timestamp that when passed into the following PHP function would produce an output of 24-04-2008.

print date('d-m-Y');

To see this for yourself, either use the above code - or try putting the timestamp into an online UNIX timestamp converter.

The people who run MySQL report that this is the intended behaviour - UNIX_TIMESTAMP should return the timestamp in UTC rather than a particular timezone. As one person points out, UTC is quite similar to GMT but is not as GMT turns to BST in the summer. So to get the behaviour you'd expect you'll need to try a little trickery.

The first option I can think of is to convert the timezone when retrieving the field, but before using it in UNIX_TIMESTAMP such as:

SELECT UNIX_TIMESTAMP(CONVERT_TZ(myDateField, 'GMT', '+00:00')) FROM myTable;

For CONVERT_TZ to function correctly you will need to make sure the timezone table in the MySQL database is fully populated with timezone information. There are instructions on how to do this available in the MySQL Reference Manual.

The next possible option I've not been able to try, but it might be possible to change your timezone to UTC so that all times are in +00:00 so that no changes are made to the data when it is pulled out. Unfortunately I can't guarantee that will work, but there is a third option and that is to do some processing on dates after they have been returned by MySQL.

The other option I can think of is to effect the change after it's been retrieved using PHP or whatever language you use. Whichever language it is that you use, the way to do it is to detect if the date is during daylight savings for that particular year (requires some calculations such as leap years, and if it's a historic date it also has to take into account the changes to the calendar if it's too early). So, for PHP we might try something along the lines of this:

function utc2gmt ($dt) {
   list($year, $month, $day) = split('-', date('Y-m-d', $utcStamp));
   list($hour, $min, $sec) = split(':', date('h:i:s', $utcStamp));

   // get time difference from UTC
   $gmt_different = mktime(0,0,0,4,1,1970,1) - gmmktime(0,0,0,4,1,1970,0);
   
   $month = intval($month);
   $day = intval($day);
   $year = intval($year);
   $hour = intval($hour);
   $min = intval($min);
   $sec = intval($sec);
   
   if ($month >= 4 && $month =< 8) {
      // since $gmt_different contains value in seconds, convert to hours when deducting
      $hour -= ($gmt_different / 3600);
   }
   
   return date('Y-m-d H:i:s', mktime($hour, $min, $sec, $month, $day, $year));
}

The point of this is that if the date falls into BST it automatically removes 1 hour so that the date appears correct, however since I don't know how to correctly calculate if a date falls into daylight savings time for a timezone I've made it count any month between April and August inclusive. A better, though less efficient way of doing this is to use a function provided by the people that make ADOdb. The function, adodb_date exists as part of their Date/Time library and takes a format and a UTC timestamp as parameters and returns the formatted date in the server's local time. Exactly what is needed, so a very useful function from a very handy library. As with PHP's date function, it will only work for dates between 1901 and 2038. So up until 2038 you should get on okay with this function, past then - well what can be done then is anyone's guess; we have 30 years to solve that one in (millennium bug part 2 anyone?).