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

Question

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.

Answer 1

I would try editing the projectConfiguration, and add something like (untested and unverified):

    $databaseManager = new sfDatabaseManager($this->configuration);
    $connection = $databaseManager->getDatabase($options['connection'])->getConnection();
    $diff = $connection->execute("SET time_zone = 'UTC'");

Note, $options and $this->configuration is only available in a task, so maybe this should be hardcoded (I believe default is 'doctrine').

Answer 2

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.)

Answer 3

If you want to do this at the Symfony application level, the way is adding this to the config/settings.yml

all:
  .settings:
    default_culture: en_US
    default_timezone: Asia/Kolkata