Server/database time zone correct in admin wrong in database

[badarac]

The site is hosted on a server in Central time and the site is in Eastern time. I've adjusted the timezone offset to America/New_York and the time displays correctly in the admin/server info and the server and database. When looking at the timestamps in the database tables however they are an hour off. Looking in phpmyadmin variables for the database it shows the time_zone variable is set to SYSTEM which is the default and indicates that system time zone is CST. Does anybody know what I need to look at to get the database to enter the correct times?

[Ajeh]

If you have access to a php.ini or .user.ini (such as what I use with GoDaddy) you could add:

date.timezone = "America/New_York"

[lhungil]

This will NOT affect the operation of Zen Cart. Zen Cart 1.5+ automatically handles the timezone differences by setting the timezone used by the database connection to match the timezone configured for PHP. This is why in the Zen Cart 1.5+ admin all of the dates appear "localized" to "America/New_York" after you have changed the default timezone for PHP / Zen Cart.


This should also not affect database imports / exports done via phpMyAdmin as long as when exporting the option "Dump TIMESTAMP columns in UTC" is enabled (default on most hosting providers). This does (as you noted) affect viewing and editing the date / time via phpMyAdmin.


If you really must have the database server's timezone changed, you will need to ask your hosting provider. This can be changed on most VPS / dedicated accounts... However on most shared hosting the MySQL server's default timezone and phpMyAdmin will be locked.

[Ajeh]

I also did this on another server using the files from Zen Cart v1.5.4 for my Zen Cart v1.5.1:
/includes/extra_datafiles/set_time_zone.php

<?php
/**
 * @package initSystem
 * @copyright Copyright 2003-2013 Zen Cart Development Team
 * @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
 * @version GIT: $Id: Author: DrByte  Sat Nov 2 01:21:28 2013 -0400 New in v1.5.2 $
 */
/*
 * Set time zone
*/
if (version_compare(PHP_VERSION, 5.3, '>='))
{
  // put your timezone here. Refer to http://www.php.net/manual/en/timezones.php
  $TZ = 'America/New_York';  // eg: 'Europe/Oslo'


  /**
   * MAKE NO CHANGES BELOW THIS LINE
   *
   * The following will take the timezone you specified above and apply it in your store.
   * If you didn't specify one, it will try to use the setting from your server's PHP configuration
   */
  if ($TZ == '') {
    $TZ = date_default_timezone_get();
  }
  if ($TZ != '') {
    putenv('TZ=' . $TZ);
    @date_default_timezone_set($TZ);
  }
}

/your_secret_admin/includes/extra_configures/use_catalog_time_zone.php

<?php
/**
 * @package admin
 * @copyright Copyright 2003-2013 Zen Cart Development Team
 * @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
 * @version GIT: $Id: Author: DrByte  Sat Nov 2 01:21:28 2013 -0400 New in v1.5.2 $
 */
// get time zone settings from catalog-side file
include (DIR_FS_CATALOG . '/includes/extra_configures/set_time_zone.php');

and now I have the correct time for both the display and database updates ...

[DrByte]

While using those files from v1.5.4 can help, since the site is using v1.5.1 from 2012 it doesn't have the necessary code in the database query-factory to also set the timezone in the database. The query_factory code from 1.5.4 is not compatible with 1.5.1 because they use different parts of PHP. The v1.5.1 code uses the obsolete no-longer-supported mysql_ functions, and the v1.5.4 code uses the modern supported mysqli_ functions.

THE BEST SOLUTION IS TO UPGRADE, not just throw patches at it.

[lhungil]

Perhaps I am reading the original post by the OP incorrectly. I interpreted the original post as "I set the timezone in (PHP or Zen Cart) to America/New_York and the time displays correctly in the admin/server info (in Zen Cart's administrative interface)" and "When looking at the timestamps in the database tables (via phpmyadmin) they are off an hour"...

To me, it sounded like the OP already had the timezone working correctly in PHP and Zen Cart.. And the OP was wondering why the timestamps are off by an hour inside the MySQL server when accessed via phpmyadmin... But perhaps I misread the original post?


NOTE: The file "set_time_zone.php" was originally introduced in Zen Cart 1.5.0 and as long as the site is using PHP 5.3+ (and has a valid timezone configured in PHP or the file) will set TZ. In Zen Cart 1.5.0's query_factory class, if TZ is set it will be automatically used for the database connection (unless disabled). However as noted by DrByte, Zen Cart 1.5.4 includes updated methods and classes better handling timezones. I would also recommend an upgrade in the near future.

[badarac]

But perhaps I misread the original post?

Nope, you got it right.

[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.