Jump to content

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


PWaddict
 Share

Recommended Posts

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.

Link to comment
Share on other sites

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 ?

  • Like 7
Link to comment
Share on other sites

  • PWaddict changed the title to [SOLVED] How to set timezone for each PW website hosted on 1 server?
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 ?

  • Like 3
Link to comment
Share on other sites

  • 4 weeks later...
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' ";
}

 

  • Like 1
  • Thanks 1
Link to comment
Share on other sites

  • PWaddict changed the title to [SOLVED] How to set database timezone?
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

Link to comment
Share on other sites

  • PWaddict changed the title to How to set database timezone?
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.

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

  • PWaddict changed the title to [SOLVED] How to set database timezone?
  • PWaddict changed the title to [SOLVED] How to set database timezone on Shared Servers?
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. 

Link to comment
Share on other sites

  • 3 years later...
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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...