Set MySQL session variable - timezone - using Doctrine 1.2 and Zend Framework

Question

Got a Zend Framework web application using Doctrine 1.2 connecting to a MYSQL 5.1 server.

Most data needs to be entered and displayed in local timezone. So following the advice here, I'd like (I think) to set PHP and MySQL to use UTC and then I'll handle the conversion to local time for display and to UTC prior to insert/update. [If this is totally goofy, I'm happy to to hear a better approach.]

So, how do I tell Doctrine to set the MySQL session to UTC? Essentially, how do I tell Doctrine to issue the MySQL command SET SESSION time_zone = 'UTC'; when it opens the connection?

Thanks in advance!

Will you consider to start mysql with `--timezone=UTC` ? http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html ? Otherwise, you would need to look for http://www.doctrine-project.org/api/orm/1.2/doctrine/doctrine_query.html . — ajreal, Nov 26 '10 at 05:17
@ajreal Thanks for the quick response. Sadly, a shared hosting environment, so no control of the MySQL server startup. ;-( So, really, you think I need to manually issue my own custom query? That would be a disappointment since that would force a db connection, killing the lazy-connect for pages that don't require it. Maybe subclass the Doctrine_Manager or Doctrine_Connection to issue the command right after the connection is made. Just thinking out loud. — David Weinraub, Nov 26 '10 at 05:53
Best bet is to modify the doctrine connection script, and see whether possible to issue your own query along with the connection (if there is no obvious method to support that) — ajreal, Nov 26 '10 at 06:02
Maybe something using a Doctrine_Event or Doctrine_Connection::postConnect()? Any insight here appreciated. — David Weinraub, Nov 26 '10 at 07:13

score 3 · Accepted Answer · answered Nov 26 '10 at 08:43

It appears that this can be done by attaching to the Doctrine_Connection object an Doctrine_EventListener with a postConnect() method.

Doctrine ORM for PHP - Creating a New Listener

Something like:

class Kwis_Doctrine_EventListener_Timezone extends Doctrine_EventListener
{
    protected $_timezone;

    public function __construct($timezone = 'UTC')
    {
        $timezone = (string) $timezone;
        $this->_timezone = $timezone;
    }

    public function postConnect(Doctrine_Event $event)
    {
        $conn = $event->getInvoker();
        $conn->execute(sprintf("SET session time_zone = '%s';", $this->_timezone));
    }
}

[Using sprintf() here is probably amateurish, but I couldn't figure out how to do a proper parameter bind. (embarrassed smiley).]

Then in my app's Bootstrap.php, I have something like:

protected function _initDoctrine()
{
    // various operations relating to autoloading
    // ..

    $doctrineConfig = $this->getOption('doctrine');

    $manager = Doctrine_Manager::getInstance();
    // various boostrapping operations on the manager
    // ..

    $conn = Doctrine_Manager::connection($doctrineConfig['dsn'], 'doctrine');
    // various boostrapping operations on the connection
    // ..

    // Here's the good stuff: Add the EventListener
    $conn->addListener(new Kwis_Doctrine_EventListener_Timezone());

    return $conn;
}

[One slightly off-topic note: on my local development machine, I kept running into a MySQL error in which no timezone I entered seemed to be accepted. Turns out that the standard MySQL install creates all the timezone tables in the mysql database, but doesn't actually populate them; you need to populate them separately. More info at MySQL site.]

Set MySQL session variable - timezone - using Doctrine 1.2 and Zend Framework

1 Answers1