You must use UTC timestamp conversion in PHP / MySQL for each connection

I want to be independent of the time zone configured on the server, so in the script I set the time zone as follows:

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

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

Then on a PHP site, I select something from the database as follows:

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 use the field type Timestamp, not Datetime.

Not what I get is a timestamp, which in the future is 2 hours behind.

The mysql document says:

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

So this leads me to 3 possible cases:

  • , : - 2 ( 1 )

  • , : - UTC, -1 , .

  • , : !

, , PHP:

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

, mysqls, , FROM_UNIXTIME - .

, , select?

- ? , UTC mysql, / ?

+3
1

.

, , , MySql .

SET time_zone = 'UTC';

, , :

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

:

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

All Articles