Site timezone not matching MySQL timezone

[Moxamint]

Hi,

I believe the timezone used for a Phoenix/osCommerce website can be specified in the configuration file. However, the site timezone could mismatch the timezone MySQL server uses when the hosting company has your site and their MySQL server hosted at different geological locations. That means the time logged for the order received will not match the timezone I set up in the configuration file.

Is it possible to solve this issue from the core code or by an add-on/module, instead of having to use a MySQL VPS that will cost me extra money?

Thanks in advance for your help.

Eddy

[ReneH4]

Jack on the other forum had an add-on for this. I think he's not here.

[Moxamint]

Jack on the other forum had an add-on for this. I think he's not here.

I'll see if I can find him...

Thanks!

[Moxamint]

@ReneH4 It's not one of Jack's add-on, but I have found the one that does the job. That add-on, however, is quite intrusive to the core code because it changes pretty much all occurrences of now() function. I think I need to figure out something else to do what I wanted...

Eddy

[Moxamint]

Hi Again,

I googled and found this piece of information:

For MySQL, set your preferred UTC/GMT timezone by running the following query before any other. This example uses the PHP mysql_query function, where -X- is your UTC/GMT hour offset). For example, you would use -5 for New York City since it's 5 hrs behind GMT.

mysql_query("SET time_zone = '-5:00';");

This query must run at the start of each script execution as the change lasts for only as long as your script runs, or you're connected to the MySQL server.

What exactly does that mean? Is there a way to implement it on a Phoenix site?

Any help would be much appreciated.

Thanks, Eddy

[ecartz]

You'd have to edit includes/functions/database.php

After

Code: Select all

    @mysqli_query($$link, 'SET SESSION sql_mode=""');

add

Code: Select all

    @mysqli_query($$link, "SET time_zone = '-5:00';");

Adjust the -5:00 as necessary.

[Kofod95]

Could it not be a hook, using a language-define?

//Daniel

[ecartz]

Could it not be a hook

Note that if you want it to run first, before any regular database queries, it would have to run before the hooks are loaded, which of course is before the language constants are loaded. And I think that changing time zones based on language is risky. It would be better to use the database time always in UTC and adjust it in the PHP.

You could conceivably run it as a system hook. That would be after the hooks query but could be before anything else. That might be sufficient. includes/hooks/shop/system/_00_db_timezone.php

Code: Select all

class hook_shop_system__00_db_timezone.php {

  public function listen_startApplication() {
    tep_db_query("SET time_zone = '-5:00';");
  }

}

[Kofod95]

I realised that changing with the language was stupid after I wrote.. I also realised that it would not be early enough to be correct (or at least as described in the Google-result-qoute), but thank you for the example with a hook as well as another useful explanation!

//Daniel

[Moxamint]

Could it not be a hook

Note that if you want it to run first, before any regular database queries, it would have to run before the hooks are loaded, which of course is before the language constants are loaded. And I think that changing time zones based on language is risky. It would be better to use the database time always in UTC and adjust it in the PHP.

You could conceivably run it as a system hook. That would be after the hooks query but could be before anything else. That might be sufficient. includes/hooks/shop/system/_00_db_timezone.php

Code: Select all

class hook_shop_system__00_db_timezone.php {

  public function listen_startApplication() {
    tep_db_query("SET time_zone = '-5:00';");
  }

}

Thank you very much @ecartz! Then how do I detect the timezone that the website is on so that the MySQL time offset can be determined automatically without having to manually type it in the hook file?

Thanks, Eddy

Edit: Answer to my self: I guess SQL SELECT @@global.time_zone and PHP date_default_timezone_get() & timezone_offset_get() will do it?