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

Question

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?

Answer 1

The answer was quite trivial.

The approach above is just fine, MySql just didn't know ANY timezones.

You can test this with the command SET time_zone = 'UTC';

If you have the same problem as the questioner you should recieve the following error:

#1298 - Unknown or incorrect time zone: 'UTC'

This can be easily fixed with the following command:

mysql_tzinfo_to_sql /usr/share/zoneinfo/|mysql -u root mysql -p