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
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 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?
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'.
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!
The Laravel portal for problem solving, knowledge sharing and community building.
The community