Set Mysql Time Zone On Db Connections [Solved]

In sharing hosts time is on GMT typically.

How to change mysql time zone in ActiveRecord?

SET time_zone = timezone;

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

I think you have to set it globally,so…

Try this in protected/config/main.php

return array(


    'timeZone' => 'Europe/Athens'


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.

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.

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 :)

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

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

the setTimeZone calls the php function "date_default_timezone_set"

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.

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

class MyDbConnection extends CDbConnection


  protected function initConnection($pdo)



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

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





   . . .


 		"SET time_zone = '+4:30'"


   . . .




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'



   . . .


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



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);

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
  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'", ),