Jump to content

Archived

This topic is now archived and is closed to further replies.

Moxamint

Database Timezone

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.


Help shape the future of Phoenix; join the Phoenix Club

Share this post


Link to post
Share on other sites

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.

Share this post


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

 

Share this post


Link to post
Share on other sites

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

Share this post


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

Share this post


Link to post
Share on other sites

@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

Share this post


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

Share this post


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


Help shape the future of Phoenix; join the Phoenix Club

Share this post


Link to post
Share on other sites

There was more to that post :(

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


Help shape the future of Phoenix; join the Phoenix Club

Share this post


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

Share this post


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


Help shape the future of Phoenix; join the Phoenix Club

Share this post


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

Share this post


Link to post
Share on other sites

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.

Share this post


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

Share this post


Link to post
Share on other sites

@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


Get the latest Responsive osCommerce CE (community edition) here .

Share this post


Link to post
Share on other sites

@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


Get the latest Responsive osCommerce CE (community edition) here .

Share this post


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

Share this post


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

Share this post


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

Share this post


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

Share this post


Link to post
Share on other sites

×