CODEWITHSUNDEEP
phpmysqlsymfony1doctrinesymfony-1.4
scotts
scotts

Reputation: 4097

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.

Upvotes: 6

Views: 3704

Answers (5)

Musa Haidari
Musa Haidari

Reputation: 2267

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 }

Upvotes: 0

Maxim Mandrik
Maxim Mandrik

Reputation: 447

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' }

Upvotes: 0

Prasad
Prasad

Reputation: 1820

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

Upvotes: 0

Jeremy Kauffman
Jeremy Kauffman

Reputation: 10413

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

Upvotes: 5

Bouke
Bouke

Reputation: 12198

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

Upvotes: 1

Related Questions