Server/database time zone correct in admin wrong in database

[badarac]

Upgrading is not always the best answer. This site just finished an upgrade that was started prior to 1.5.3 being released. When we picked it back up I looked at going with 1.5.4 but because some mods were not available and merging changes required was going to be a major undertaking we decided to complete the upgrade to 1.5.1 before we tackled 1.5.4.

Perhaps I haven't made myself clear on what the problem is. I have installed the time zone fix mod (by Gilby) which is available for 1.5.1. Since installing the mod the correct time appears in admin/server info for both the database and server.

Server OS: Linux 2.6.32-458.23.2.lve1.2.45.el6.x86_64    	Database: MySQL 5.5.41-cll-lve
Server Date: 02/02/2015 13:15:41   	Database Date: 02/02/2015 13:15:41

The tz environment variable indicates that the timezone is set as expected

Environment Variable	Value
TZ 	America/New_York

The problem is that when looking at the date purchased timestamp on the admin/orders menu it is one hour behind. Looking at the database the times in the order records, customer info, others are all one hour behind even though the correct time is displayed in the admin. The emails sent also contain the wrong time.

I'm trying to understand why even though the timezone seems to be set correctly the database is writing the incorrect time.

[lhungil]

... The problem is that when looking at the date purchased timestamp on the admin/orders menu it is one hour behind. ...

So when you access "/admin/orders.php" the date listed for the orders is NOT correct?

... I'm trying to understand why even though the timezone seems to be set correctly the database is writing the incorrect time. ...

The database server internally stores dates using the timezone configured for the MySQL server. MySQL server supports specifying the local timezone on a per-connection basis. MySQL functions such as "now()" will use the per-connection timezone if set, otherwise they will use the server's configured timezone.

You need the files posted by Ajeh. These files add the "TZ" environment variable (not just the variable), adjust the PHP timezone, and query_factory will then pick up and use the timezone for the database connection. The files from Ajeh should be used instead of the "time zone fix mod (by Gilby)". The files do require PHP 5.3 (or newer).

I still have one client on Zen Cart 1.5.1 with PHP 5.3 configured for 'America/Chicago' (client's timezone) and the MySQL server configured for 'America/New_York' (hosting provider's timezone)... Only timezone related modifications are the files posted by Ajeh... And the client sees the correct time in "/admin" and "/admin/orders.php"...

As the account is on shared hosting, "phpMyAdmin" and the MySQL server's timezone are not changeable. So in cPanel accessing "phpMyAdmin" shows all the dates off by an hour. This is normal and expected. Just note the earlier caveat on how to export using "phpMyAdmin" to retain the correct date and time.


NOTE 1: I was wrong earlier. While query_factory contains the necessary changes in Zen Cart 1.5.0+... I just downloaded a clean copy of Zen Cart 1.5.0 and 1.5.1 and files posted by Ajeh relating to this fix were not added prior to Zen Cart 1.5.2. My local copy had the files posted by Ajeh already applied (among other things)

NOTE 2: And yes, the site mentioned above is slated for an upgrade in the very near future.

[badarac]

Thanks for that. I used Ajeh's code and it works correctly now. Just one note. The folder is incorrect for the first code block. It should be /includes/extra_configures/set_time_zone.php not /includes/extra_datafiles/set_time_zone.php.

[Pacuk]

I wish there was a setting in My Store Configuration for time zone adjustment. Then it would be easier to remember to modify it.