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.

Jeremy Kauffman · Accepted Answer · 2010-09-21T15:58:10.517

5

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

edited Sep 21 '10 at 15:58

answered Sep 21 '10 at 14:01

Jeremy Kauffman

10,293
5
42
52

The first gives an error "there is no open connection in ...", but the second one works great, thanks. Note: I guess "UTC" isn't a valid time zone on my machine at least, so I changed it to "+0:00". – scotts Sep 21 '10 at 15:37

score 1 · Answer 2 · answered Sep 21 '10 at 09:08

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

answered Sep 21 '10 at 09:08

Bouke

11,768
7
68
102

Not working out of the box and I don't know enough about Symfony to troubleshoot it very well ($this->configuration is empty in this context..) – scotts Sep 21 '10 at 15:33
Solutions provided here do not have to be working out of the box; I only pointed you in the right direction for finding an answer (which would be jeremy's in this case). – Bouke Sep 21 '10 at 16:36
I didn't meant to imply they did, only that I didn't spend much time troubleshooting it (because there was another answer that worked immediately). Thanks. – scotts Sep 22 '10 at 14:58

score 0 · Answer 3 · answered Sep 22 '10 at 14:55

0

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

answered Sep 22 '10 at 14:55

Prasad

1,822
3
23
40

Can you add the documentation link as well please? – Musa Haidari Oct 05 '21 at 11:40

score 0 · Answer 4 · answered Nov 28 '18 at 14:47

Sorry for the answer not on MySQL, but on Oracle DB. I searched for data on Oracle DB for a long time, but found nothing. This is the only topic that also discusses the problem, but only in MySQL.

Perhaps, someone will help, for Oracle DB:

In file config/services.yaml

For Symofony 4:

services:
    Doctrine\DBAL\Event\Listeners\OracleSessionInit:
        arguments:
            - TIME_ZONE: 'UTC'
        tags:
            - { name: doctrine.event_listener, event: postConnect }

For Symfony 2 and 3:

services:
    oci8.listener:
        class: Doctrine\DBAL\Event\Listeners\OracleSessionInit
        arguments:
            - TIME_ZONE: 'UTC'
        tags:
            - { name: doctrine.event_listener, event: postConnect }

Or, you can write the arguments object to yaml as:

arguments: { TIME_ZONE: 'UTC' }

score 0 · Answer 5 · answered Oct 05 '21 at 12:24

For MySQL 8 and Symfony 5, you can add the following to config/services.yaml

services:
    Doctrine\DBAL\Event\Listeners\SQLSessionInit:
        arguments:
            - 'SET TIME_ZONE="+04:00"'
        tags:
            - { name: doctrine.event_listener, event: postConnect }

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

5 Answers5