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.
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';
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.
The following is the syntax to get the current time zone of MySQL. mysql> SELECT @@global. time_zone, @@session.
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'"
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'"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With