Back

Set default MySQL timezone on connect?


There is a system timezone setting in the app.php config file, and that sets the PHP session timezone on each page load.

I cannot see an equivalent timezone setting for the database. So with MySQL, each time I connect to the database, I would like eloquent to set the database session to use UCT. This needs to be set regardless of what the server timezone or the default MySQL timezone is set to. Is there an standard or simple way to do this in L5.1?

The reason for doing this, is to ensure created_at and updated_at are set to the correct UTC time. Unless the database session that is connected is also set to UTC, then these timestamp columns will end up being set to that localtime, and will be reported back to Laravel as a different time if the server timezone needs to be changed for any reason.

zodthepossum replied 2 years ago

I've been having problems with this also, in particular with PHP being out of sync with my System and Laravel 5.1 (running on virtual server with apache 2.4 ubuntu 14.04 - this was causing very strange problems in the application

After seeing some posts on Stack, I set my timezone in app.php to 'Australia/Sydney', rather than UTC

then in command line on the server edited the php.ini file

sudo nano /etc/php5/apache2/php.ini

un-comented out & added Australia/Sydney

date.timezone = Australia/Sydney

Clocks at least now are in sync, when I add a record to the database the date time is correct

thiver replied 2 years ago

You can add the following key to your mysql connection in the config:

 'options' => array(
                \PDO::MYSQL_ATTR_INIT_COMMAND => 'SET time_zone = \'+00:00\''
            )

This example sets your PDO database connection to UTC. So if the database itself runs in another timezone, your app wil always get the timestamps as UTC.

Altiano replied 1 year ago

thiver said:

You can add the following key to your mysql connection in the config:

'options' => array(
               \PDO::MYSQL_ATTR_INIT_COMMAND => 'SET time_zone = \'+00:00\''
           )

This example sets your PDO database connection to UTC. So if the database itself runs in another timezone, your app wil always get the timestamps as UTC.

@thiver what other options do we have for mysql connection? where can I found the documention of the options?

shahidnajam replied 1 year ago

The best way is to set 'timezone' for mysql connection in config/database.php

For example

        'mysql' => [
            'driver'    => 'mysql',
            'host'      => env('DB_HOST', 'localhost'),
            'database'  => env('DB_DATABASE', 'forge'),
            'username'  => env('DB_USERNAME', 'forge'),
            'password'  => env('DB_PASSWORD', ''),
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
            'strict'    => false,
            'timezone'  => '+00:00'
        ],

in my.cnf i have timezone set to '+08:00'

You can test with the following query

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);

From Mysql console, I see '08:00:00', and from Laravel app, i see ''00:00:00'.

rafal replied 4 months ago

I had an additional problem - my MySQL server didn't have timezone information installed, so I couldn't simply use the same timezone name as for my application, e.g. "Europe/London".

So I put this at the beginning of my config/database.php:

// Determine timezone for mysql.
$dateTimeZoneApp = new DateTimeZone(env('APP_TIMEZONE'));
$dateTimeZoneUtc = new DateTimeZone("UTC");
$dateTimeApp = new DateTime("now", $dateTimeZoneApp);
$dateTimeUtc = new DateTime("now", $dateTimeZoneUtc);

$hourOffset = round($dateTimeZoneApp->getOffset($dateTimeUtc) / 3600);
$offsetString = $hourOffset > 0
	? "+" . str_pad($hourOffset, 2, '0', STR_PAD_LEFT) . ":00"
	: "-" . str_pad($hourOffset, 2, '0', STR_PAD_LEFT) . ":00";

And now I have a timezone offset string, for instance "+02:00", that I can use like in @thiver's solution.

(Please note it doesn't account for offsets other than full hour, but could be modified to do so if necessary.)


Sign in to participate in this thread!



We'd like to thank these amazing companies for supporting us