Skip to content
Advertisement

How do I set a MysQL variable (time_zone) from within doctrine / symfony?

I would like to know how I can set the MySQL time zone to UTC (SET time_zone = 'UTC') from within Symfony/Doctrine, so when I call a UNIX_TIMESTAMP() function on a DATETIME field in my query, it returns the UTC unix time and not unix time in the server’s time zone.

How can I do this, either automatically upon every connection, or manually before these types of queries where the timezone makes a difference?

BTW, I need to do this conversion within the MySQL query and not the app so I can GROUP BY an interval that requires epoch time.

Advertisement

Answer

You can do this via the configureDoctrineConnection callbacks that gets called in ProjectConfiguration`:

public function configureDoctrineConnection(Doctrine_Connection $connection)
{
  $connection->exec('SET time_zone = "UTC"');
}

There may be issues with this if you’re using multiple connections.

(Answer edited to remove additonal method that was flawed.)

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement