Set Mysql Time Zone On Db Connections [Solved]

Yii 1.1 General Discussion
1

In sharing hosts time is on GMT typically.

How to change mysql time zone in ActiveRecord?

2 

SET time_zone = timezone;

https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

CDbConnection::$initSQLs can be used to execute the above statement automatically on each connection.

3

I think you have to set it globally,so…

Try this in protected/config/main.php


return array(

    ...

    'timeZone' => 'Europe/Athens'

    ...
4

You should keep your app tz to utc/0:00, that’s for php and mysql, always.

Then when you retrieve the data or display it, take into consideration the user timezone and convert accordingly. Even if your app uses utc+2 for example, it still needs to save the data as utc+00 and somewhere at init make the conversion.

That’s the only correct way to do it if you care about date accuracy, and you’ll see in the long run this is a win, think at apps with multiple users from multiple timezones, etc.

Just my 2 cents.

5

I was looking for a option in db component.

my current solution is run below command in beforeSave model event . is it a suitable practice?


Yii::app()->db->createCommand("SET time_zone = '+4:30'")->execute();

I’m looking for best dynamic way to set time zone and daylight saving time.

6

Hi again

In your way you can set the timezone conditionally and in specific(s) controller(s)/action(s) you want.

In my way you can set timezone in every request globally

PS: thanks for your voting :)

7

How to use your way? where is place for setting timezone(executing set time_zone command)?

8

timeZone calls Internally (via setter) the setTimeZone of the CApplication class

the setTimeZone calls the php function "date_default_timezone_set"

9

My friends, i know this. My mean is, how to it affect on MySQL time_zone?

I want


$this->createTime = new CDbExpression ('NOW()')

run according to user time zone.

10

It can be set dynamically by overriding CDbConnection::initConnection():




class MyDbConnection extends CDbConnection

{

  protected function initConnection($pdo)

  {

	parent::initConnection($pdo);

	if (Yii:app()->user->hasState('timezone') {

  	$pdo->exec('SET time_zone = ...');

	}

  {

}
11 



 'db'=>array(

   . . .

   'initSQLs'=>array(

 		"SET time_zone = '+4:30'"

 ),

   . . .

),
12

Sorry,

PHP and Mysql have separated time system, so

if you want to synchronize php and mysql time in specific timezone use both of two settings


'timeZone' => 'Europe/Athens'

....

'db'=>array(

   . . .

   'initSQLs'=>array(

                "SET time_zone = '+3:00'" //for my country (Greece)

 ),
13

http://www.yiiframework.com/forum/index.php/topic/53473-set-mysql-time-zone-on-db-connections-solved/page__view__findpost__p__246221

:)

14

Also you could check the results with this code:


$r1 = Yii::app()->db->createCommand("select now()")->queryScalar();

$r2 = date('m/d/Y h:i:s a', time()));

echo ($r1);

echo ($r2);
15

The recommendation to use a numeric timezone may result in incorrect times during daylight saving time.
I would recommend the following:

  1. ensure that your instance of mysql has the timezone tables populated per https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html
  2. in protected/config/main.php before returning the literal array, create a variable for the timezone, eg
    $config_tz = 'America/Denver' ;
  3. then within the array you can ensure that both PHP and MySQL will use the same timezone
    at the top of the array:
    'timeZone' => $config_tz ,
    inside ‘db’=>array(
    'initSQLs' => array( "SET time_zone = '$config_tz'", ),