Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Set the MySQL time zone for the current connection

Hello,

I have created the following code to set the time zone for each connection in MySQL and it works perfectly.

My question is if there is any better way to do this?

/**
 * Database connection is created based in the parameters defined in the configuration file
 */
$di->setShared(
    'db',
    function () {
        $config = $this->getConfig();

        $eventsManager = new EventsManager();

        $logger = new FileLogger($config->application->logDir . 'db.log');

        // Only apply once per connection
        $timeZoneDefined = false;

        // Listen all the database events
        $eventsManager->attach(
            'db:beforeQuery',
            function ($event, $connection) use (&$logger, &$eventsManager, &$timeZoneDefined) {
                /*
                 * Log the current query (VERY IMPORTANT LOG FIRST)
                 */
                $logger->log(
                    $connection->getSQLStatement(),
                    Logger::INFO
                );

                /*
                 * Set MYSQL TimeZone for all queries
                 */
                if ($timeZoneDefined === false) {
                    $connection->setEventsManager(new EventsManager()); // Temp NULL
                    $connection->execute("SET time_zone = '+00:00'"); // Underground SQL query!!!
                    $connection->setEventsManager($eventsManager); // Restore event manager
                    $timeZoneDefined = true;
                }

                // Let everything take its course...
            }
        );

        $class = 'Phalcon\Db\Adapter\Pdo\\' . $config->database->adapter;
        $params = [
            'host' => $config->database->host,
            'username' => $config->database->username,
            'password' => $config->database->password,
            'dbname' => $config->database->dbname,
            'charset' => $config->database->charset
        ];

        if ($config->database->adapter == 'Postgresql') {
            unset($params['charset']);
        }

        $connection = new $class($params);

        // Assign the eventsManager to the db adapter instance
        $connection->setEventsManager($eventsManager);

        return $connection;
    });

Thank you!



59.6k
Accepted
answer
edited Sep '17

In case of MySQL, it's far better alternative to do simply:

 $tz = 'Europe/Stockholm',
 $charset = 'utf8mb4';

$params['options'] = [
  \PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES {$charset}, time_zone = '{$tz}'", //pay attention to single quotes here
  //\PDO::MYSQL_ATTR_INIT_COMMAND => "SET time_zone = '+00:00'", //in your desired case
  \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_OBJ, //set fetch mode to objects
   ];

Excellent, it's a good solution.

Thank you!