[solved] Database time vs PHP time

[psy]

First time I've ever encountered this warning message and unsure how to resolve.

Scenario:

1. Customer is a long-time PW client in Australia who is moving to the UK.
2. The web host created a new account hosted in Germany and copied all the AU files and DB to the new hosting account/location, Germany being their closest hosting centre to the UK.
3. Time diff between Germany and UK is 1hr
4. Time diff between Australia and Germany is 10hrs
5. Site is working with new URL, etc
6. The new PHP time zone needs to be "Europe/London" and $config->timezone is set to same

Times are important as the client will be taking online appointment bookings.

How do I match the DB time to the new UK time zone?

Solution:

In 'site/config.php' immediately after the database connection info:

$config->dbInitCommand = "SET NAMES '{charset}', time_zone = 'Europe/London' ";

Where {charset} can be utf8 or utf8mb4 depending on your setup

[bernhard]

This is how you can set the DB timezone: https://stackoverflow.com/questions/930900/how-do-i-set-the-time-zone-of-mysql

If you don't have access to that talk to your hosting provider.

[Marty Walker]

I just wanted to add something for anyone who searches and lands here.

With the host I'm on, even if the PHP timezone is set to (in my case) 'Australia/Sydney' and the PW installer matches that, the dates are still out. The only way for me to install PW without modding the config each and every time is to set the PW installer to UTC. I don't know if this is the right or wrong way to go about it but it seems to work.

[JamesFletcher]

Thanks! I’ll update MySQL timezone, appreciate the clear guidance!

Edit: My problem is resolved.

Edited June 11 by JamesFletcher

[prestoav]

I was about to post about this subject as I have the same issue when I found this chain...

I found the solution posted by @psy resulted in a 500 server error for me. After a support chat with Hostinger's chat bot (presumably AI...) it seems that it is not possible to set the Database time on some Cloud / Shared hosting services - hence the 500 error. They recommend they following hook be added to ready.php to set it at page load:

// Set correct Database time
$wire->addHookAfter('ProcessPageView::execute', function() {
    $this->wire('database')->exec("SET time_zone = 'Europe/London'");
});

Now this does work. However, I raised concern that this seems to set the DB time every time a page loads and that this might unnecessarily increase processing overhead. However, they state it is not possible to set it a session level so has to be done at page load level.

It seems to be running OK on an (admittedly) low traffic site. Anyone have any further thoughts on the impact of this though?

[psy]

35 minutes ago, prestoav said:

I found the solution posted by @psy resulted in a 500 server error for me.

Thanks for reporting. My solution followed all the available advice. Times change... pun intended 😉

[prestoav]

Just now, psy said:

Thanks for reporting. My solution followed all the available advice. Times change... pun intendend 😉

Haha!

Actually, I see now that the fix they suggested only works for front end pages (and that will help form submissions I guess)? However, it does not apply to admin editing and the error is still there when you first login.

Ah well...

[Marty Walker]

My hosting provider had a server provisioned in Sydney but the database time was set to Perth, so it was behind by 3/4 hours (although some here would say years 🙂 ). Until they fixed that I included the following file (sorry, can't remember from which post I got it) from the config.php and it sorted out errors in the admin. I didn't notice a hit on speed either.

<?php namespace ProcessWire;


// Tell PHP to use this timezone as the default when a timezone API call doesn't specify a zone.
// Change to your local time zone name: https://www.php.net/manual/en/timezones.php
date_default_timezone_set('Australia/Sydney');

// Initialize database connection with session time zone based on PHP's time zone.
$dbTimeZone = (function() {
	// What is the timezone offset (in minutes) from UTC for the current time in the current timezone?
    $minutes = (new \DateTime())->getOffset() / 60;
    $sign = ($minutes >= 0) ? '+' : '';
    $h = str_pad(intdiv($minutes, 60), 2, '0');
    $m = str_pad($minutes % 60, 2, '0');
    return $sign . $h . ':' . $m;
})();
$config->dbInitCommand = "SET NAMES '{charset}', time_zone = '$dbTimeZone' ";

?>