Jump to content

Archived

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

jd_wgd

Date/Time OK - except for Orders

Recommended Posts

Hi,

 

I have an AU site hosted on a US service. The time on the site, as well as the date format, was wrong until I changed the application_top.php files to include

// define AU time
putenv("TZ=Australia/Sydney");

 

and the english.php files to include

// Australian date settings
@setlocale(LC_TIME, 'en_AU.ISO_8859-1');

define('DATE_FORMAT_SHORT', '%d/%m/%Y'); // this is used for strftime()
define('DATE_FORMAT_LONG', '%A %d %B, %Y'); // this is used for strftime()
define('DATE_FORMAT', 'd/m/Y'); // this is used for date()
define('DATE_TIME_FORMAT', DATE_FORMAT_SHORT . ' %H:%M:%S');

 

The problem is: the date & time that appear for the Order Creation date and any status updates made to Orders are still in US time!?!?! How and why is this - where is this time definition hiding??? I thought I had all my bases covered :(

 

Please help!

Share this post


Link to post
Share on other sites

Jeannette I had the same problem as you, this is how I fixed it.

 

I believe the reason why it doesn't update the date is because the host is using the mysql now() function to put the date into the database. Naturally, it uses the date of the host - the country it is in. To get around this problem you need to do these steps:

 

Place the code below in the two english.php found in the Catalog and Admin sections. Note: Change the setlocale en_AU to the country you are in, you are in Australia so we use the the code below.

 

 ?
?putenv("TZ=Australia/Sydney");
?@setlocale(LC_TIME, 'en_AU.ISO_8859-1');
?$aus_date_time = date("Y-m-d H:i:s");

 

Next place the code below in checkout_process.php at line 92 from

 

'date_purchased' => 'now()',

to

'date_purchased' => $aus_date_time,

 

 

and at line 111 from

'date_added' => 'now()',

to

'date_added' => $aus_date_time,

 

Next you have to go to orders.php, found under the Admin directory and change this code. At around line 40. Replace now() with '".$aus_date_time."'

 

 ? ? ? ?if ( ($check_status['orders_status'] != $status) || tep_not_null($comments)) {
? ? ? ? ?tep_db_query("update " . TABLE_ORDERS . " set orders_status = '" . tep_db_input($status) . "', last_modified = now() where orders_id = '" . (int)$oID . "'");

 

to this

 

 ? ? ? ?if ( ($check_status['orders_status'] != $status) || tep_not_null($comments)) {
? ? ? ? ?tep_db_query("update " . TABLE_ORDERS . " set orders_status = '" . tep_db_input($status) . "', last_modified = '".$aus_date_time."' where orders_id = '" . (int)$oID . "'");

 

 

That should do it.

 

Oscommerce uses Mysql now() to insert the current date in the database. You have to replace any other now()'s in Oscommerce with the above variable - $aus_date_time - to put the current date into the database in your local time in any other section of oscommerce (inserting products for example or whatever else.)

 

This should apply to any other country, just put it in for the language file you are using.

 

I hope that helps ;)

 

Thanks,

 

:blink: Brainsplus :huh:

Share this post


Link to post
Share on other sites

Thanks BrainsPlus, really nice of you to take the time for such a detailed explanation!

 

Cheers,

J

Share this post


Link to post
Share on other sites

Yes, thanks! - just what I've been looking for too.

 

Just in case anyone needs it, the UK version is:

 

@setlocale(LC_TIME, 'en_GB.ISO_8859-1');
PUTENV("TZ=Europe/London");

 

 

You can also use PUTENV("TZ=GMT"); - not sure yet if the difference is that 'Europe/London' gives you summer time automatically ... will have to wait a month or two and see ...

 

Thanks again

 

:rolleyes:

Share this post


Link to post
Share on other sites

Could you please tell me the Arizona (Mountain time zone) version of PUTENV?

 

putenv("TZ=US/?????");

 

Thank you!

Share this post


Link to post
Share on other sites

Well, this is a guess ... but it's listed on my server simply as US/Arizona :D

 

You can see all the possibles if you have access on your server to go to:

 

/usr/share/zoneinfo/

 

These are the top level entries in /zoneinfo/:

 

Africa

America

Antarctica

Arctic

Asia

Atlantic

Australia

Brazil

Canada

CET

Chile

CST6CDT

Cuba

EET

Egypt

Eire

EST

EST5EDT

Etc

Europe

Factory

GB

GB-Eire

GMT

GMT-0

GMT+0

GMT0

Greenwich

Hongkong

HST

Iceland

Indian

Iran

Israel

Jamaica

Japan

Kwajalein

Libya

MET

Mexico

Mideast

MST

MST7MDT

Navajo

NZ

NZ-CHAT

Pacific

Poland

Portugal

PRC

PST8PDT

ROC

ROK

Singapore

SystemV

Turkey

UCT

Universal

US

UTC

W-SU

WET

Zulu

 

But quite a few of these (like Europe, US) are folders, so have quite a few more entries inside them.

 

Europe has:

 

Amsterdam

Andorra

Athens

Belfast

Belgrade

Berlin

Bratislava

Brussels

Bucharest

Budapest

Chisinau

Copenhagen

Dublin

Gibraltar

Helsinki

Istanbul

Kaliningrad

Kiev

Lisbon

Ljubljana

London

Luxembourg

Madrid

Malta

Minsk

Monaco

Moscow

Nicosia

Oslo

Paris

Prague

Riga

Rome

Samara

San_Marino

Sarajevo

Simferopol

Skopje

Sofia

Stockholm

Tallinn

Tirane

Tiraspol

Uzhgorod

Vaduz

Vatican

Vienna

Vilnius

Warsaw

Zagreb

Zaporozhye

Zurich

 

And the US has:

 

Alaska

Aleutian

Arizona

Central

East-Indiana

Eastern

Hawaii

Indiana-Starke

Michigan

Mountain

Pacific

Pacific-New

Samoa

 

There's also a folder full for 'America':

 

Adak

Anchorage

Anguilla

Antigua

Araguaina

Aruba

Asuncion

Atka

Barbados

Belem

Belize

Boa_Vista

Bogota

Boise

Buenos_Aires

Cambridge_Bay

Cancun

Caracas

Catamarca

Cayenne

Cayman

Chicago

Chihuahua

Cordoba

Costa_Rica

Cuiaba

Curacao

Danmarkshavn

Dawson

Dawson_Creek

Denver

Detroit

Dominica

Edmonton

Eirunepe

El_Salvador

Ensenada

Fort_Wayne

Fortaleza

Glace_Bay

Godthab

Goose_Bay

Grand_Turk

Grenada

Guadeloupe

Guatemala

Guayaquil

Guyana

Halifax

Havana

Hermosillo

Indiana

Indianapolis

Inuvik

Iqaluit

Jamaica

Jujuy

Juneau

Kentucky

Knox_IN

La_Paz

Lima

Los_Angeles

Louisville

Maceio

Managua

Manaus

Martinique

Mazatlan

Mendoza

Menominee

Merida

Mexico_City

Miquelon

Monterrey

Montevideo

Montreal

Montserrat

Nassau

New_York

Nipigon

Nome

Noronha

North_Dakota

Panama

Pangnirtung

Paramaribo

Phoenix

Port_of_Spain

Port-au-Prince

Porto_Acre

Porto_Velho

Puerto_Rico

Rainy_River

Rankin_Inlet

Recife

Regina

Rio_Branco

Rosario

Santiago

Santo_Domingo

Sao_Paulo

Scoresbysund

Shiprock

St_Johns

St_Kitts

St_Lucia

St_Thomas

St_Vincent

Swift_Current

Tegucigalpa

Thule

Thunder_Bay

Tijuana

Tortola

Vancouver

Virgin

Whitehorse

Winnipeg

Yakutat

Yellowknife

 

:rolleyes:

Share this post


Link to post
Share on other sites
Jeannette I had the same problem as you, this is how I fixed it.

 

I believe the reason why it doesn't update the date is because the host is using the mysql now() function to put the date into the database. Naturally, it uses the date of the host - the country it is in. To get around this problem you need to do these steps:

 

Place the code below in the two english.php found in the Catalog and Admin sections. Note: Change the setlocale en_AU to the country you are in, you are in Australia so we use the the code below.

 

 ?
?putenv("TZ=Australia/Sydney");
?@setlocale(LC_TIME, 'en_AU.ISO_8859-1');
?$aus_date_time = date("Y-m-d H:i:s");

 

Next place the code below in checkout_process.php at line 92 from

 

'date_purchased' => 'now()',

to

'date_purchased' => $aus_date_time,

 

 

and at line 111 from

'date_added' => 'now()',

to

'date_added' => $aus_date_time,

 

Next you have to go to orders.php, found under the Admin directory and change this code. At around line 40. Replace now() with '".$aus_date_time."'

 

 ? ? ? ?if ( ($check_status['orders_status'] != $status) || tep_not_null($comments)) {
? ? ? ? ?tep_db_query("update " . TABLE_ORDERS . " set orders_status = '" . tep_db_input($status) . "', last_modified = now() where orders_id = '" . (int)$oID . "'");

 

to this

 

 ? ? ? ?if ( ($check_status['orders_status'] != $status) || tep_not_null($comments)) {
? ? ? ? ?tep_db_query("update " . TABLE_ORDERS . " set orders_status = '" . tep_db_input($status) . "', last_modified = '".$aus_date_time."' where orders_id = '" . (int)$oID . "'");

 

 

That should do it.

 

Oscommerce uses Mysql now() to insert the current date in the database. You have to replace any other now()'s in Oscommerce with the above variable - $aus_date_time - to put the current date into the database in your local time in any other section of oscommerce (inserting products for example or whatever else.)

 

This should apply to any other country, just put it in for the language file you are using.

 

I hope that helps ;)

 

Thanks,

 

:blink: Brainsplus :huh:

Thanks for the info, but if you plug in $aus_date_time instead of using now() function, is this causing files which use now() to update the database to not update anymore?

Share this post


Link to post
Share on other sites

this is the error i get after changing all instances of now() with $aus_date_time

Warning: Safe Mode: Cannot set environment variable 'TZ' - it's not in the allowed list in /home/httpd/vhosts/clantech.com.au/httpdocs/catalog/includes/application_top.php on line 15

 

Warning: Safe Mode: Cannot set environment variable 'TZ' - it's not in the allowed list in /home/httpd/vhosts/clantech.com.au/httpdocs/catalog/includes/languages/english.php on line 19

1064 - You have an error in your SQL syntax near '>= expires_date and expires_date > 0' at line 1

 

select specials_id from specials where status = '1' and >= expires_date and expires_date > 0

 

[TEP STOP]

Share this post


Link to post
Share on other sites

As far as I know, safe mode needs to be off on the server for OSC to run properly. You might try asking your host to add PUTENV/TZ to the 'allowed' list if they want to keep safe mode = on.

Share this post


Link to post
Share on other sites
As far as I know, safe mode needs to be off on the server for OSC to run properly. You might try asking your host to add PUTENV/TZ to the 'allowed' list if they want to keep safe mode = on.

Thank you

Share this post


Link to post
Share on other sites

In paypal ipn the date_added seems to be generated in catalog/includes/classes/paypal/ipn.php.

 

The nearby lines are:

        'notify_version'      => $this->key['notify_version'],
       'verify_sign'         => $this->key['verify_sign'],
       'date_added'          => 'now()');

 

What is the correct thing to change this now() to?

 

Should it look like this:

        'notify_version'      => $this->key['notify_version'],
       'verify_sign'         => $this->key['verify_sign'],
       'date_added'          => $aus_date_time);

Thanks

Tim

Share this post


Link to post
Share on other sites

I have used the mods suggested in this thread to make my times appear more in line with my time. I have noticed however that the times in the entries for Customers-orders in the tool box do not reflect my local time.

 

Can anyone please tell me what further modifications need to be done to make them use the correct time?

 

TIA

Share this post


Link to post
Share on other sites

OK, I think I'm getting the hang of this thing now.

 

First I used my word processor to search inside all my .php files (the backup ones on my HD) to find which files contained - now() - I then replaced each of those occurrences with '".$aus_date_time."' on the server.

 

Interesting thing I noticed. There were two formats:

 

first was - now() - which needed to be changed to - '".$aus_date_time."' -

the other was - 'now()' - which needed to be changed to - $aus_date_time -

 

Everything seems to be working alright (so far).

Share this post


Link to post
Share on other sites
Everything seems to be working alright (so far).

Well sadly, my bubble of joy has burst. :(

 

After replacing all occurences I now get an error when trying to update the product description and a customer has reported the same error when trying to create an account.

 

the error is

1054 - Unknown column '$aus_date_time' in 'field list'

 

update products_description set products_name = ' ... [ my product description was shown here] ... ', products_url = $aus_date_time where products_id = '99' and language_id = '1'

 

[TEP STOP]

 

 

I have posted in this thread: http://forums.oscommerce.com/index.php?showtopic=93298

 

but so far the only suggestion has been to 'forget about the time thing' because to fix the problem means fiddling with the database tables.

 

Any other suggestions before I revert back to my saved version?

 

(sorry for the cross-post)

Share this post


Link to post
Share on other sites

Hi Ausipodskins,

 

Interesting piece of code you have there.

 

update products_description set products_name = ' ... [ my product description was shown here] ... ', products_url = $aus_date_time where products_id = '99' and language_id = '1'

 

Products_url is a date? A url is not a date, but an Uniform Resource Locator, it's an Internet address (for example, http://www.oscommerce.com) I don't think now() should of been there, what file is it from? If I'm wrong and it is meant to be there, I'm interested to know why a products_url would be used as a date. :)

 

Brainsplus

 

Nikola Tesla. Thank him for world wide electricity, and you wouldn't be using this PC today, if it wasn't for him.

Share this post


Link to post
Share on other sites

thanks Brains. that was my concern as well.

 

the problem is that the 'code' I quote is the error message that osC throws up. I have no idea where it is making the connection between the url and the date. I have done a complete text search through all my php files (on a backup copy of course) for occurrences of "$aus_date_time" and can't find any that are connected with "products_url".

 

I have basically removed all references to $aus_date_time except for the few suggested at the beginning of this thread.

Share this post


Link to post
Share on other sites

I have tried it with replacing the now() with date('ymdhis') works fine so far too.

 

@setlocale(LC_TIME, 'en_GB');

@PUTENV('TZ=Europe/London');

@mktime('0,0,0,1,1,1970');

 

this in the english.php

 

Works fine all together for the UK

 

 

i noticed that not all servers are accepting the PUTenv without the mktime!

Share this post


Link to post
Share on other sites
Can anyone help with this problem?

your replacements with now() in ' ' are beeing traced in the file \includes\functions\database.php thats why they are in ' '

 

I just found out that by changing in this file the 'insert' command part the now() with date('ymdhis') is enough to get the order with the correct time, all the other changes needs to be tested.

 

So it needs a lot of sorting out now..

 

 

Regards,

Share this post


Link to post
Share on other sites

Hi all,

 

I have made these changes and everything seems to work great, except for the database backup timestamp.

 

I guess its getting this from the file created date.

 

Is there any way to modify this to GMT on a US hosted site?

 

Thanks

 

Chris

Share this post


Link to post
Share on other sites

×