需要在每个连接的基础上在PHP / MySQL中使用UTC时间戳转换

I want to be independent from the timezone configured on a server so in a script I set the time zone like this:

mysql_query("SET time_zone = '".date_default_timezone_get()."';");

The server is currently configured to Europe/Moscow which currently is UTC+4

Then in a PHP site I select something from the database like this:

date_default_timezone_set('Europe/Berlin');
$sth = $dbh->prepare("SET time_zone = '".date_default_timezone_get()."';");
$sth->execute();
$sth = $dbh->prepare("SELECT  * from logs WHERE time like '2011-06-1%'");
$sth->execute();

I am using Timestamp field type and not Datetime.

Not what I get displayed is a timestamp that is 2 hours too far in the future.

The mysql doc says:

Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval.

So this brings me to the 3 possible cases:

• Storage conversion works, select does not: No - because then The timestamp would be 2 (or 1 in winter) hours too far in the past

• Storage conversion does not work, but select does: No - because then i would see UTC which is -1 hours which is not the case

• Storage conversion doesn't work, select conversion doesn't work: Looks just like it!

Now the timestamp I write into the database is constructed and written PHP side:

$hourprec = "Y-m-d H:00:00";
$hour = date($hourprec); // mysql compatible
...
REPLACE INTO logs (time,...) VALUES('".$hour."','"....

I can imagine that this makes problems with mysqls time conversion because it comes as a string and I should do FROM_UNIXTIME or something.

But shouldn't it work at least with the select then?

Am I missing something? How do I have to do it if I want to store and read timestamps correctly in UTC in a mysql database but read/write them in scripts that have different time zones?