[SOLVED] How to set database timezone on Shared Servers?

[PWaddict]

I have 1 server in Canada and multiple client's pw websites from different countries hosted on that server. How can I set on each website to have client's correct timezone?

I thought $config->timezone in site/config.php should do that but it doesn't. For example 1 website is from a client in Greece so I'm using this: $config->timezone = 'Europe/Athens'; but when a page is getting saved on the "last modified" info says 6 hours ago. That's the hour difference between Greece and Canada.

[Ivan Gretsky]

Maybe setting correct locale in config will do?

[PWaddict]

1 hour ago, Ivan Gretsky said:

Maybe setting correct locale in config will do?

Nope. Already tried that. Timezone and locale are different things.

[netcarver]

@PWaddict

You could try calling date_default_timezone_set() in config.php.

[PWaddict]

51 minutes ago, netcarver said:

@PWaddict

You could try calling date_default_timezone_set() in config.php.

I tried date_default_timezone_set('Europe/Athens'); but still on the backend when a page is getting saved it says 6 hours ago on the "last modified".

[BitPoet]

What happens if you set the following in site/config.php:

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

 

[PWaddict]

According to the following @ryan post from 2013 the info field is not affected by the timezone. 

 

[Jonathan Lahijani]

User-specific timezones is on Ryan's todo list:

https://github.com/processwire/processwire-issues/issues/270

[PWaddict]

9 hours ago, BitPoet said:

What happens if you set the following in site/config.php:

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

 

That result in a 500 server error. I changed it to the following and now I'm getting the proper time on backend's creation, modification, installation times etc.

$config->dbInitCommand = "SET NAMES '{charset}', time_zone = '+02:00' ";

Your DatetimeAdvanced module helped me with that ?

[BitPoet]

15 hours ago, PWaddict said:

That result in a 500 server error.

Then your server doesn't have time zone data installed. If you can add that, you can assign a zone name and don't have to adapt the offset every time DST changes.

Glad to hear you got it solved though ?

[PWaddict]

On 11/2/2018 at 1:46 PM, BitPoet said:

Then your server doesn't have time zone data installed. If you can add that, you can assign a zone name and don't have to adapt the offset every time DST changes.

I asked my hosting company to install the timezone tables and they said it's not possible on shared server and changing manually the offset is bad but then I found about the capital "I" on date format where we can easily detect if the DST changes. So here is the best solution:

if (date('I', time())) {
  $config->dbInitCommand = "SET NAMES '{charset}', time_zone = '+03:00' ";
} else {
  $config->dbInitCommand = "SET NAMES '{charset}', time_zone = '+02:00' ";
}

 

[mel47]

23 hours ago, PWaddict said:

I asked my hosting company to install the timezone tables and they said it's not possible on shared server and changing manually the offset is bad but then I found about the capital "I" on date format where we can easily detect if the DST changes. So here is the best solution:

if (date('I', time())) {
  $config->dbInitCommand = "SET NAMES '{charset}', time_zone = '+03:00' ";
} else {
  $config->dbInitCommand = "SET NAMES '{charset}', time_zone = '+02:00' ";
}

 

Wow, thanks so much @PWaddict! I saw this post by chance this morning. Those hour differences on my server always annoyed me but I never searched to solve them. And you just give the perfect solution! ?

Mel

[PWaddict]

On 11/27/2018 at 4:46 AM, PWaddict said:

if (date('I', time())) {
  $config->dbInitCommand = "SET NAMES '{charset}', time_zone = '+03:00' ";
} else {
  $config->dbInitCommand = "SET NAMES '{charset}', time_zone = '+02:00' ";
}

 

Now that DST is active my solution doesn't seem to work. I'm still getting the non-DST value.

[PWaddict]

Here is the proper solution:

// site/config.php
date_default_timezone_set('Europe/Athens');

if (date('I')) {
  $config->dbInitCommand = "SET NAMES '{charset}', time_zone = '+03:00' ";
} else {
  $config->dbInitCommand = "SET NAMES '{charset}', time_zone = '+02:00' ";
}

 

[adrian]

This is worth a read: https://stackoverflow.com/questions/930900/how-do-i-set-the-time-zone-of-mysql

Remember that you are better off using timezone names than hours because of daylight saving / summer time changes.

 

[PWaddict]

8 hours ago, adrian said:

Remember that you are better off using timezone names than hours because of daylight saving / summer time changes.

 

Timezone names are not installed on shared servers. That's why I'm detecting DST with php and set the proper offset time on database. 

[Brendan Kidwell]

On 11/1/2018 at 4:09 PM, PWaddict said:

That result in a 500 server error. I changed it to the following and now I'm getting the proper time on backend's creation, modification, installation times etc.

$config->dbInitCommand = "SET NAMES '{charset}', time_zone = '+02:00' ";

Your DatetimeAdvanced module helped me with that ?

This works better for me, and accounts for daylight savings time switches -- it looks at PHP's timezone offset for the current date and time in the current timezone:

// Add these lines to your config.php file.

// 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('America/New_York');

// 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' ";

// Tell ProcessWire to use this timezone.
// (You'll find this in your config file already under the comment "Installer: Time zone setting".)
$config->timezone = 'America/New_York';

I checked it and I see that the actual timestamps being recorded in the database are in UTC timezone, as they should be. They get formatted with the correct LOCAL timezone offset when displayed in the admin screens.

[matjazp]

@Brendan KidwellAre you sure that the padding of zeros should be performed on the right? 

[Brendan Kidwell]

On 5/2/2022 at 3:25 AM, matjazp said:

@Brendan KidwellAre you sure that the padding of zeros should be performed on the right? 

Correct. My code is wrong. I was searching for this answer today and didn't even remember I'd written this.

I have revised my answer just now:

// Add these lines to your config.php file.

// 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('America/New_York');

// Initialize database connection with session time zone based on PHP's time zone.
$config->dbInitCommand = (function() {
    $charset = 'utf8';

	// 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(abs($minutes), 60), 2, '0', STR_PAD_LEFT);
    $m = str_pad(abs($minutes) % 60, 2, '0', STR_PAD_LEFT);
    $dbTimeZone = "{$sign}{$h}:{$m}";

    return "SET NAMES '{$charset}', time_zone = '{$dbTimeZone}'";
})();

// Tell ProcessWire to use this timezone.
// (You'll find this in your config file already under the comment "Installer: Time zone setting".)
$config->timezone = 'America/New_York';