4

I have set the Yii->application->configuration->timezone to UTC and have model attributeBehaviors to insert/update the timestamp using PHP's time() method automatically.

The issue, is that the timestamp that is saved to the db is in the future and when I perform a search query for records from today, they aren't being returned if they were created between 10 and midnight as the timestamp is actually tomorrow.

How should I create the record and perform a search query?

Thanks.

Insane Skull
  • 9,220
  • 9
  • 44
  • 63
Mike Pearson
  • 505
  • 1
  • 6
  • 18

1 Answers1

1

It seems you have a difference between PHP any MySQL timezones.

Try to execute the following:

echo date_default_timezone_get() . "<br>";
echo Yii::$app->formatter->timeZone . "<br>";
echo Yii::$app->formatter->defaultTimeZone  . "<br>";
echo Yii::$app->db->createCommand('SELECT @@time_zone')->queryScalar()  . "<br>";
echo Yii::$app->db->createCommand('SELECT @@system_time_zone')->queryScalar()  . "<br>";

(the query is for MySQL DBMS, if you have another one - the query will be also another)

When you see the difference - you have to set the same timezone in PHP and MySQL (for PHP, for Yii2)

SilverFire
  • 1,582
  • 13
  • 22
  • I have researched this very conflict and thought I could bypass the conflict was to globally set the Yii/PHP timezone and not use MySQL time functions. I use the attributeBehaviors to save `time()` which should use the PHP timezone. Is that correct? I will run this test tonight. – Mike Pearson Oct 23 '15 at 21:16
  • This is the response: UTC UTC UTC SYSTEM GMT+4 – Mike Pearson Oct 24 '15 at 01:35
  • 1
    Here is your problem. You create date in UTC, and MySQL think that it is date in UTC+4. You have to [change MySQL timezone](http://stackoverflow.com/questions/930900/how-to-set-time-zone-of-mysql) to UTC or set the correct timezone in Yii2 for each connection. To do it open the config.php (where the DB user and password is set) and set timezone like shown [here](https://gist.github.com/SilverFire/6f98d2605a6574bd02f7) – SilverFire Oct 24 '15 at 09:14
  • I found the snippet for Yii2 to modify the MySQL timezone on connect last night. Now that they are synced, there isn't any issus. Thanks. – Mike Pearson Oct 25 '15 at 03:17