marcus welz

Zend_Db: Setting MySQL's timezone per connection

I have a Linux server with a system timezone of ET (US/Eastern). But I also have a web application that needs to run in a timezone of PT (US/Pacific). Of course that's not a problem at all. I just set the timezone in my web application's bootstrap:

date_default_timezone_set('America/Los_Angeles'); // Pacific timezone

Now I have another problem; the database. Sometimes I use PHP to generate dates such as date('Y-m-d H:i:s', strtotime('-2 minutes')). Other times I insert records and use new Zend_Db_Expr('NOW()');. But because MySQL isn't aware that I'd like to use pacific time, dates end up being inconsistent and are off by three hour.

It's a fairly easy fix, though, with a bit of logic added to a custom MySQL database adapter:

<?php

/**
 * @see Zend_Db_Adapter_Pdo_Mysql
 */
require_once 'Zend/Db/Adapter/Pdo/Mysql.php';

/**
 * MySQL PDO adapter extended to set the timezone
 */
class My_Db_Adapter_Pdo_Mysql extends Zend_Db_Adapter_Pdo_Mysql
{
    /**
     * @var bool
     */
    protected $_initialized = false;

    /**
     * Connects to the database.
     *
     */
    protected function _connect()
    {
        parent::_connect();

        if (!$this->_initialized) {
            $this->_initialized = true;

            if ($this->_config['timezone']) {

                // Requires PHP 5.2+
                $dtz = new DateTimeZone($this->_config['timezone']);
                $offset = $dtz->getOffset(new DateTime('NOW')) / 60 / 60;

                $this->query(sprintf("SET time_zone = '%d:00'", $offset));
            }
        }
    }
}

To kick this all off my bootstrap contains:

$config = array();
$config['host'] = 'localhost';
$config['username'] = 'username';
$config['password'] = 'password';
$config['dbname'] = 'mydatabase';
$config['timezone'] = 'America/Los_Angeles';
$config['adapterNamespace'] = 'My_Db_Adapter';

$db = Zend_Db::factory('PDO_MYSQL', $config);
Zend_Db_Table::setDefaultAdapter($db);

date_default_timezone_set('America/Los_Angeles');

And now I am free to continue my habit of inconsistency when specifying dates.