CODEWITHSUNDEEP
symfonydoctrine
the_nuts
the_nuts

Reputation: 6054

Symfony Doctrine - set time zone

I have multiple Symfony applications running on the same sever, and every application may need a different time zone.

For php, it's doable by using different fpm pools and setting the time zone in the pool configuration:

php_admin_value[date.timezone] = America/New_York

but for MySQL, I need to issue the statement:

"SET time_zone = 'America/New_York';

as the first query after connected, or add to the $options array in the PDO constructor:

PDO::MYSQL_ATTR_INIT_COMMAND => "SET time_zone = 'America/New_York';"

How can this be done?

Upvotes: 5

Views: 10030

Answers (3)

Andrei Sosnin
Andrei Sosnin

Reputation: 730

As of version 3.5 of Doctrine DBAL, the solution proposed by SilvioQ is deprecated.

Doctrine documentation recommends implementing a middleware class for the database driver instead. An example of such implementation can be found within the DBAL code itself: src/Logging/Middleware.php

Upvotes: 1

jlh
jlh

Reputation: 4667

To extend on SilvioQ's answer, I wrote the following snippet to inherit the time zone from the PHP environment, assuming that date_default_timezone_set() was previously called to set the correct time zone:

This solution has an advantage over named time zones (like "America/New_York") when those named time zones are not available within MySQL/MariaDB and you have no control over the server.

public function postConnect(ConnectionEventArgs $args) {
    $tz = new DateTimeZone(date_default_timezone_get());
    $offset = $tz->getOffset(new DateTime('now'));
    $abs = abs($offset);
    $str = sprintf('%s%02d:%02d', $offset < 0 ? '-' : '+', intdiv($abs, 3600), intdiv($abs % 3600, 60));
    $args->getConnection()->exec("SET time_zone = '$str'");
}

Upvotes: 2

SilvioQ
SilvioQ

Reputation: 2072

You can use PostConnect Doctrine event ... https://www.doctrine-project.org/projects/doctrine-dbal/en/2.9/reference/events.html#postconnect-event

This is useful to configure the connection before any sql statement is executed.

An example may be:

<?php

namespace App\EventListener;

use Doctrine\DBAL\Event\ConnectionEventArgs;

/**
 * My initializer
 */
class  MyPdoInitializerListener
{
    public function postConnect(ConnectionEventArgs $args)
    {
        $args->getConnection()
            ->exec("SET time_zone = 'America/New_York'");
    }
}

Don't forget to add the listener to services.yaml

# services.yaml

    # ...

    App\EventListener\MyPdoInitializerListener:
        tags:
            - { name: doctrine.event_listener, event: postConnect }

Upvotes: 6

Related Questions