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


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
    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
            function ($event, $connection) use (&$logger, &$eventsManager, &$timeZoneDefined) {
                 * Log the current query (VERY IMPORTANT LOG FIRST)

                 * 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') {

        $connection = new $class($params);

        // Assign the eventsManager to the db adapter instance

        return $connection;

Thank you!

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!