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, / ?