时区问题mysql

My server is in the US and I'm in the UK.

I store timestamp values on a mysql server using the default CURRENT_TIMESTAMP. So when entries are added to my table they're stamped with the current time. This is the server time(US) and then converted into UTC by mysql.

When I retrieve the values I want them displayed in the current timezone (UK). So in my PHP I use the following function to set the current timezone:

mysql_query("SET time_zone = timezone;");

This seems to work as when I ask mysql to echo server and session timezones I get SYSTEM and -08:00 respectively.

HOWEVER, when I later print my timestamp values in the same PHP script they are outputted in US time.

Getting results in PHP:

$result = mysql_query("SELECT ID,DateTime,Name,Comment,Location,Rating FROM table1 WHERE Latitude<$latup AND Latitude>$latdown AND Longitude<$lngup AND Longitude>$lngdown ORDER BY DateTime DESC")

while(($row = mysql_fetch_array( $ratingresult )) && ($i++ < 1)) {
    echo "<tr><td>"; 
    echo $row['DateTime'];
    echo "</td><td>"; 
    echo $row['Name'];
    echo "</td><td>";
    echo $row['Comment'];
    echo "</td><td>";
    echo $row['Location'];
    echo "</td><td>";
    echo $row['Rating'];
    echo "</td><td>";
    rate($row, $lat, $lng);
    echo "</td></tr>"; 
} 

Any help would be really appreciated!

Working with mulitple timezones in MySQL can be tricky.

You have to be real careful with changing the timezone on a per-session basis like that. In addition to changing the displayed result you retrieve from a TIMESTAMP field (effectively the integer number of seconds since 01-01-1970 00:00:00 GMT) , it will also change the data inserted into DATETIME fields (effectively a hard coded string), and/or how NOW() is evaluated.

If it's not too late, and your application would allow it, I'd recommend setting the server/global timezone to UK (and make the respective change in the my.cnf too):

e.g. mysql> SET GLOBAL time_zone = timezone; from http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html

Or, alternatively, just convert-to/use DATETIMEs across the board -- making sure when you do the conversion which timezone your session is in.

I have solved the problem by using PHP.

$dt_obj = new DateTime($row['DateTime'], new DateTimeZone('America/Chicago')); 
$dt_obj->setTimezone(new DateTimeZone('Europe/London')); 
echo $dt_obj->format('d-m-Y H:i:s');

This works because my server in the US is on CST = Chicago timezone. I also removed all my attempts to modify the mysql timezone. I'm sure changing the server timezone is a solution for some people but I'm on a shared server so don't have permissions.

My question now is - Is there a way to get my website to work globally? ie by detecting the users runtime? I assume this would need javascript.

Please review this code to execute for right result:

date_default_timezone_set('Europe/London');
mysql_query("SET time_zone = '".date('P')."';");