Timezone in Database connection

Hello!

I would like to set my own timezone to MySQL database connection, because timezone of MySQL server is different from timezone of PHP and my application clients.

I try to use PDO driver instead of mysqli, and set dsn like this:
$this->load->database('mysql://root:@localhost/my_db_name?timezone=0:00');


But it's not work. It's retrieve date from TIMESTAMP field type using mysql server(hosting) timezone.
I saw the mysql query log, and there is no query to set timezone session timezone like this : "SET timezone = +0:00;"

I expected to set some $db['options'] setting value in config/database.php file, but there is no one.

How can I setup the MySQL connection timezone without core modification? And what kind of driver should I use: "mysqli" or "pdo"?

Thank you!

$this->db->query('SET time_zone = "-07:00"');

Expert Website Development - Temecula, CA
Community Auth For CodeIgniter 3

Thank you for answer!
I understand, that I can execute this query. But it should be in every connection to database I create.
But I would like to set it 1 time in 1 place, application/config/database.php file is perfect.
Is it possible?

You could put it in a hook or the class constructor of MY_Controller.

Expert Website Development - Temecula, CA
Community Auth For CodeIgniter 3

You could put it in a hook or the class constructor of MY_Controller.

So, there is no way for CI 3.0.4 version to setup timezone in application/config/database.php file, for example in $db['some_key'] value or dsn string ?

You could put it in a hook or the class constructor of MY_Controller.

So, there is no way for CI 3.0.4 version to setup timezone in application/config/database.php file, for example in $db['some_key'] value or dsn string ?

I'm not aware of any other way. Perhaps somebody else will reply with a better answer.

So, for instance, in config/hooks:

Code:

// Set timezone for PHP
$hook['pre_system'][] = function(){
    // Los Angeles, California, USA
    date_default_timezone_set('America/Los_Angeles');
};
// Set timezone for MySQL based on PHP timezone
$hook['post_controller_constructor'] = function(){
    $CI =& get_instance();
    // Assuming DB is autoloaded
    $CI->db->query('SET time_zone = "' . date('P') . '"');
};

I think the only problem with this might be if for some reason you need MySQL timezone applied before a database session is checked. Database session is checked before the post_controller_constructor if sessions is autoloaded.

Yes, it would be really great to specify the timezone in config/database.

Expert Website Development - Temecula, CA
Community Auth For CodeIgniter 3