Database Timezone

[♥Moxamint 61]

Hi,

My company is located in Asia but my website is hosted in the States.  I managed to change the timezone of the Apache server to match my location by adding date.timezone = "Asia/Taipei" in php.ini and I have also changed the related value in configure.php for both shop and admin.  However, when an order was placed, the logged order time still used the US timezone.  The database timezone was not changed.

Is there a piece of code I should use to change the database timezone?

Thanks in advance for your time and help.

Eddy

[burt 5,421]

Difficult.  It's done on a server basis rather than individual accounts on the server.

If you are on normal shared hosting...there is no way to do that (unless the host is ok with it) as your needed changes would affect every other customer on the server...

If you are on a private server...you'd need to edit the my.cnf file to tell the server to add/remove the correct number of hours from where it is now, to suit your location.  Once amended the my.cnf file, restart the mysql server and you are good to go.

You could potentially find every occurence of NOW() in the files of your shop...and replace with something like NOW() - INTERVAL 10 HOUR - which *might* work...obviously changing 10 to the correct amount of hours you need...note I have not tested this, it came to my mind as I typing this post...if you try it, report back your findings...

Easiest way to try;

1.  perform an order
2.  open up phpmyadmin and look at the orders table, column date_purchased for the order just made.  It should be set to the time of the USA server.
3.  at this line of code in checkout_process change 'now()' to 'now() - interval 10 hour'
4.  perform another order.  
5.  again look at the orders table, column date_purchased and *fingers crossed* it should show 10 hours earlier than the previous test order.

[Jack_mcs 1,020]

Moxamint : This addon will allow the times to be correct. It hasn't been updated in some time and may need some minor adjustments to work in your shop.

[♥Moxamint 61]

2 hours ago, burt said:

Easiest way to try;

1.  perform an order
2.  open up phpmyadmin and look at the orders table, column date_purchased for the order just made.  It should be set to the time of the USA server.
3.  at this line of code in checkout_process change 'now()' to 'now() - interval 10 hour'
4.  perform another order.  
5.  again look at the orders table, column date_purchased and *fingers crossed* it should show 10 hours earlier than the previous test order.

@burt Many thanks!  Will try that.  Although I could order their MySQL VPS but that would cost extra money every month...

@Jack_mcs The add-on you pointed me to only instructed me to add define('CFG_TIME_ZONE', 'America/Los_Angeles'); in configure.php.  That would only change the timezone php script uses, but the server / database timezone won't be affected.  Maybe you meant to point me to this one Time Zone Offset - Adjust to match your instead?

 

[Jack_mcs 1,020]

You are correct. I apologize for the mistake. The one you mentioned is the one I meant.

[♥Moxamint 61]

19 minutes ago, Jack_mcs said:

You are correct. I apologize for the mistake. The one you mentioned is the one I meant.

Thanks for confirmation.  I'll look into it as well.

Cheers, Eddy

[♥Moxamint 61]

@Jack_mcs The add-on basically does what @burt suggested by moving the code to function tep_db_perform() so that the database time is altered each time the function is called.

So I guess I have my questions answered.

Thanks again, Eddy

[burt 5,421]

Did my idea work ?

[♥Moxamint 61]

On 2017/10/2 at 5:16 PM, burt said:

Easiest way to try;

1.  perform an order
2.  open up phpmyadmin and look at the orders table, column date_purchased for the order just made.  It should be set to the time of the USA server.
3.  at this line of code in checkout_process change 'now()' to 'now() - interval 10 hour'
4.  perform another order.  
5.  again look at the orders table, column date_purchased and *fingers crossed* it should show 10 hours earlier than the previous test order.

@burt Hi Gary,

Unfortunately it does not work by just changing 'now()' to 'now() - interval 10 hour' in checkout_process, because the function tep_db_perform() that will deal with the database later only recognizes 'now()' as one of the switches.  if I move 'now() - interval 10 hour' directly into function tep_db_perform(), then it works correctly.

Thanks and Happy Moon Festival,

Eddy

[burt 5,421]

case 'now()':
            $query .= 'now(), ';
            break;

to:

case 'now()':
            $query .= 'now()-interval 10 hour, ';
            break;

did do it?

This new forum sucks for quoting and coding. So bad.

[burt 5,421]

There was more to that post :(

Anyway, Happy Moon Festival to you, I just looked it up, all those lanterns!!

[♥Moxamint 61]

16 minutes ago, burt said:

 

case 'now()':
            $query .= 'now()-interval 10 hour, ';
            break;

did do it?

@burt Yes about code did it :)

16 minutes ago, burt said:

Anyway, Happy Moon Festival to you, I just looked it up, all those lanterns!!

...and moon cake, fire crackers, and the whole nine yards.

Cheers, Eddy

[burt 5,421]

Just now, Moxamint said:

@burt Yes about code did it :)

I wonder if we should put in some sort of an admin dashboard module or something which would get the time of the server and adjust accordingly.  MIght be something to look at if someone is willing.

[♥Moxamint 61]

3 minutes ago, burt said:

I wonder if we should put in some sort of an admin dashboard module or something which would get the time of the server and adjust accordingly.  MIght be something to look at if someone is willing.

Actually I just started my attempt to fix this time issue side-wise by using the old-fashioned way (core changes).  I have defined the following:

$dateServer = new DateTime("now", new DateTimeZone(date_default_timezone_get()));
$dateStore = new DateTime("now", new DateTimeZone('Asia/Taipei'));
$TimeOffset = $dateServer->format('H') -  $dateStore->format('H')

...and am on my way to change all occurrences of 'now()'...

An admin dashboard module would be extremely welcome... :)

Cheers, Eddy

[Jack_mcs 1,020]

The addon I mentioned does the same thing and already has the command needed to add a database setting to allow controlling the time in from admin. Don't see the purpose of reinventing the wheel here.

[♥Moxamint 61]

9 minutes ago, Jack_mcs said:

The addon I mentioned does the same thing and already has the command needed to add a database setting to allow controlling the time in from admin. Don't see the purpose of reinventing the wheel here.

Hi Jack,

Thanks for the comment.  1) My host in the USA could change the hosting location; 2) There is one thing called "daylight saving" and we do not have that in Taiwan.  Result: the time offset could change without my awareness.

My site generates custom order IDs based on the time orders are placed to match our ERP system, and these order IDs reset everyday.  They highly reply on server time being sync to local time of Taiwan to operate.  So if there is a way to avoid changing core code everywhere, I sure will vote for that.

Cheers, Eddy

[ArtcoInc 364]

@Moxamint

 

2 hours ago, Moxamint said:

1) My host in the USA could change the hosting location; 2) There is one thing called "daylight saving" and we do not have that in Taiwan.  Result: the time offset could change without my awareness.

Daylight Savings Time follows a well defined schedule, It should not be too difficult to incorporate that into your adjustment code.

Malcolm

[♥Moxamint 61]

3 minutes ago, ArtcoInc said:

Daylight Savings Time follows a well defined schedule, It should not be too difficult to incorporate that into your adjustment code.

https://www.timeanddate.com/time/change/usa

@ArtcoInc Thanks.  Just wish there is no such thing...

[ArtcoInc 364]

@Moxamint

IIRC, it's a carry-over from World War II, where the 'time' was adjusted to shift more of the 'working hours' into daylight, thus saving the country energy. It is probably as necessary now as the 9-month school year, where students get the summers off to help their parents work their farms.

<shrug>

Malcolm

[Jack_mcs 1,020]

2 hours ago, Moxamint said:

Hi Jack,

  So if there is a way to avoid changing core code everywhere, I sure will vote for that.

That's not possible due to the code for the database time being coded in the core files. But once the change is made and you have the setting in admin, you just have to change it when needed to adjust all of the times.

[♥Moxamint 61]

On 2017/10/7 at 1:03 AM, ArtcoInc said:

@Moxamint

IIRC, it's a carry-over from World War II, where the 'time' was adjusted to shift more of the 'working hours' into daylight, thus saving the country energy. It is probably as necessary now as the 9-month school year, where students get the summers off to help their parents work their farms.

<shrug>

Malcolm

I am wondering if there is a statistics on the actually energy saving by doing this daylight saving thing.

[ArtcoInc 364]

There are three kinds of lies:

Lies, Damned Lies, and Statistics

 

[♥Moxamint 61]

24 minutes ago, ArtcoInc said:

There are three kinds of lies:

Lies, Damned Lies, and Statistics

So I guess it has fallen into the "great idea, doesn't work" category?

Taiwan started using daylight saving in 1945 under the decision of Japanese government.  It was then ended in 1980 by the Taiwanese government.  It took the politicians so long to figure out daylight saving was merely an annoying thing.

[MrPhil 648]

13 hours ago, Moxamint said:

I am wondering if there is a statistics on the actually energy saving by doing this daylight saving thing.

It actually dates back to Benjamin Franklin (to reduce candle usage), and first appeared in WWI (in Germany, IIRC). It used to save energy when electric lighting was inefficient (incandescent bulbs only) and was the major user of electric power. Nowadays, it's believed that Air Conditioning actually uses more due to the Sun being higher in the sky when people get home, so the net is that DST uses more energy than Standard Time. Nonetheless, Congress extends DST at each "energy crisis" to show it's on top of things (ha!). Most people would be happy to have either ST or DST year round and avoid the annoying and hazardous changeovers. The outdoor recreation industry (golf courses, amusement parks, ski resorts, etc.) always do heavy lobbying to make sure DST doesn't go away, and would prefer to have it year round. I would prefer ST year round because I can't wake up and get going until the sky gets light, but maybe that's just me. Also, if it's extra dark on school mornings, more parents will be driving their kids to school rather than having them wait for the school bus in the dark (dangerous), wasting even more energy. I can remember waiting for the bus back in '74 in the pitch black.