Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to set database time zone in application.ini

I have an application in Zend Framework that has to run for a different time zone than the server. Is there an option to set the database server (MySQL in this case) time zone in application.ini?

My current options are:

resources.db.adapter = "Pdo_Mysql"
resources.db.params.charset = "utf8"
resources.db.params.driver_options.1002 = "SET NAMES utf8"
resources.db.params.host = "localhost"
resources.db.params.username = "usernam"
resources.db.params.password = "password"
resources.db.params.dbname = "databasename"

I know I can do something like SET timezone = 'Europe/London', but I really need to do it in the config file.

EDIT

Googling around I found that resources.db.params.driver_options.1002 should be setting the value for PDO::MYSQL_ATTR_INIT_COMMAND.

Therefore resources.db.params.driver_options.1002 = "SET NAMES utf8, time_zone = 'Europe/London'" should do the trick. But no luck so far.

FINAL EDIT

Found it. After a lot of searching and debugging I found the following code at Zend\Db\Adapter\Pdo\Mysql.php

if (!empty($this->_config['charset'])) {
    $initCommand = "SET NAMES '" . $this->_config['charset'] . "'";
    $this->_config['driver_options'][1002] = $initCommand; // 1002 = PDO::MYSQL_ATTR_INIT_COMMAND
}

As I have resources.db.params.charset = "utf8" in my application.ini, it was overwriting PDO::MYSQL_ATTR_INIT_COMMAND.

Deleting that line solved it.

like image 625
Esteban Avatar asked Dec 19 '10 20:12

Esteban


People also ask

How do I change the time zone on my database?

Use the ALTER DATABASE SET TIME_ZONE command to change the time zone of a database. This command takes either a named region such as America/Los_Angeles or an absolute offset from UTC. This example sets the time zone to UTC: ALTER DATABASE SET TIME_ZONE = '+00:00';

How do I change timezone in MySQL?

In MySQL the CONVERT_TZ() returns a resulting value after converting a datetime value from a time zone specified as the second argument to the time zone specified as the third argument. This function returns NULL when the arguments are invalid. A datetime. A time zone which will be converted to to_tz.

How do I get MySQL time zone?

The following is the syntax to get the current time zone of MySQL. mysql> SELECT @@global. time_zone, @@session.


2 Answers

I'm answering my own question just to close this, as the response is already in one of the edits.

resources.db.params.charset

and

resources.db.params.driver_options.1002

as I had in my application.ini cannot be used together, as resources.db.params.charset ovewirites driver_options.1002.

If you need to set the timezone, remove resources.db.params.charset and pass the timezone in the charset in driver_options. E.g.:

resources.db.params.driver_options.1002 = "SET NAMES utf8, time_zone = 'Europe/London'"
like image 127
Esteban Avatar answered Sep 21 '22 19:09

Esteban


This works for me:

resources.db.adapter = "Pdo_Mysql"
resources.db.params.driver_options.1002 = "SET NAMES 'utf8', CHARACTER SET 'utf8', time_zone = 'Europe/London'"
like image 33
k2s Avatar answered Sep 22 '22 19:09

k2s