Jump to content
PWaddict

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

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.

Share this post


Link to post
Share on other sites

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

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

 

Share this post


Link to post
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 6

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


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

 

Share this post


Link to post
Share on other sites
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. 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...