» » » How to set the application timezone and MySQL for Yii2

How to set the application timezone and MySQL for Yii2

Facebook
LinkedIn
Follow by Email
YOUTUBE
PINTEREST
2 min read

I built my own application in 2017 when I lived in the USA and all my clients were American. The timezone that I set at that time is Pacific timezone. In 2018, I moved back to Thailand and I need to set new timezone. I will share how to set the timezone in MySQL and PHP in the Yii2 application. My host is shared host so if you have the dedicated host it will work as well.

 

Set PHP timezone in Yii2 application

In Yii2 application, go to config/main-local.php and set the attributes below.

<?php
$config = [
    'timeZone' => 'Asia/Bangkok', 
]
?>

This setting is for PHP. So when you use date() PHP function, the date and time will follow the timeZone you set. You can check by using the code below in PHP file.

<?php
echo date('Y-m-d H:i:s');
echo '<br>';
echo date_default_timezone_get();
?>

For more timezone value you can find your own from this link.

 

Set database connection timezone in Yii2 application

<?php
$config['components']['db'] = [         
    'class' => 'yii\db\Connection',
    'dsn' => 'mysql:host=localhost;dbname=my_dbname',
    'username' => 'root',
    'password' => 'root',
    'charset' => 'utf8',
    'tablePrefix' => 'acc_',
    'on afterOpen' => function($event) { 
        // set 'Asia/Bangkok' timezone
        $event->sender->createCommand("SET time_zone='+07:00';")->execute(); 
    },          
];
?>

More information about the database connection timezone setting can be found here. This setting is for MySQL. You can check by using the code below in phpmyadmin or other database interface application.

SHOW VARIABLES LIKE '%time_zone%';
SELECT NOW();

 

NOTE:

If you change the MySQL timezone while you are login, just log out and log in the application again. So that the Yii2 database connection will set the timezone for the current session for you.

 

Facebook
LinkedIn
Follow by Email
YOUTUBE
PINTEREST