Cakephp set database timezone

Question

I'm setting timezone to php and mysql to internacionalize my CakePHP application.

When the server receives a request from a client, before process request, it connects to a GeoIp location server and gets the Timezone. Then I use date_default_timezone_set() to set php timezone. The problem comes up when I want to set database timezone. Once Cakephp connected, I need to execute sql query like SET time_zone='-06:00'.

In /lib/Cake/Model/Datasource/Database/Mysql.php I can see at connect() function the following code:

    try {
        $this->_connection = new PDO(
            $dsn,
            $config['login'],
            $config['password'],
            $flags
        );
        $this->connected = true;
        if (!empty($config['settings'])) {
            foreach ($config['settings'] as $key => $value) {
                $this->_execute("SET $key=$value");
            }
        }
    } catch (PDOException $e) {
        throw new MissingConnectionException(array(
            'class' => get_class($this),
            'message' => $e->getMessage()
        ));
    }

There is a $config['settings'] array that can be configured to do it. But I don't know how to fill settings array and where it's the best place to do that.

What I need is modify default datasource config on-the-fly

score 3 · Answer 1 · edited May 23 '17 at 12:32

3

You can add an additional key to config array located at app/Config/database.php like this:

public $default = array(
    'datasource'  => 'Database/Mysql',
    'persistent'  => false,
    'host'        => 'localhost',
    'login'       => 'db_user',
    'password'    => 'db_pass',
    'database'    => 'db_name',
    'prefix'      => '',
    'settings'    => array(
           'time_zone' => "'+01:00'", // note the quotes!
    )
);

Related: CakePHP switch database (using same datasource) on the fly?

edited May 23 '17 at 12:32

Community

1
1

answered Nov 12 '15 at 13:53

skywalker

826
1
10
18

Well... I know that option but, in that way, I can't connect to GeoIp Server. that -06:00 is dynamic. – Serginho Nov 12 '15 at 14:12
Not exacly that. AppController is executed before ConnectionManager sets up a connection, so I get the location of user there and what I don't know is how to get default configuration and add settings array from AppController for example. I don't need another connection because in that moment, there's no connection yet. What I need is: Modify default datasource config on-the-fly. – Serginho Nov 12 '15 at 14:28
I've posted a solution about what I really wanted to do @skywalker – Serginho Nov 17 '15 at 13:15
1

I've edited thre answer. The code was throwing a MySQL error, to make it work you have to wrap the value passed in quotes, just so MySQL treats it as a string and not an expression. See [How to set time zone of MySQL](http://stackoverflow.com/questions/930900/how-to-set-time-zone-of-mysql). Tested the suggested update and confirmed it works. – ᴍᴇʜᴏᴠ Mar 22 '16 at 12:14

score 2 · Accepted Answer · answered Nov 17 '15 at 13:11

I solved it in the following way.

First of all, adding setOptions() method into DATABASE_CONFIG class as follows:

public function setOptions ($datasource, array $options){
    $this->{$datasource} = array_merge($this->{$datasource}, $options);
}

Afterwards, extending ConnectionManager class to initialize it:

class ConnectionManagerCustomConfig extends ConnectionManager
{
    public static function initialize(){
        self::_init();
    }
}

Now I initialize the class and add new options:

ConnectionManagerCustomConfig::initialize();
$configClass = ConnectionManagerCustomConfig::$config;
$configClass->setOptions('default', array(
  'settings' => array(
    'time_zone' => $offset
  )
));

I never had the time to write something like this myself, but I thought this is the way to go. — skywalker, Nov 17 '15 at 13:21

Cakephp set database timezone

2 Answers2