Whilst the defaults work correctly on my local pc I found that on a Live server they did not and Dates/Times for Orders and Order History were not being displayed as I would like
The issue is that typically the Web server running PHP can be set to a different timezone to the database server and one cannot always get sufficient access to change the settings permanently, especially if on shared servers.
I have seen several posts here about partial solutions for this and the best I could find was the free module storetime by Fido-X http://forum.opencart.com/viewtopic.php?f=119&t=30188 but even this does not currently address the issue when the database needs changing and not just the web server/php. So even this did not fully sort my problem.
I live in the UK but my hosting is in the America/Chicago TimeZone and I wanted my reports / cut-off times etc to be in my local UK time
So at say 15:33 UK time (allowing +1 hour as we are on BST (British Summer Time) at the moment)
I would expect a UTC/GMT timezone to show/display as 14:33
However Chicago is -5 hours from UCT it showed as 9:33am
i.e. a total of -6 hours different which was very confusing/misleading with Order Histories
Now there are various approaches to using timezones and whether one should store everthing in UTC and then convert just for display purposes in the local timezone or whether to store all values as from the local timezone.
If the dates were stored in datestamp columns (which are simply Integers with on offset in seconds from a Unix start date) then any local dates would get put into the database as UTC and converted back automatically to local dates as they were retrieved. This makes for easy datetime arithmetic but has the overhead of changing these values.
However I did not want to change the database schema from the currently used datetime fields for this in case there were any side-effects elsewhere in the code (datetime columns just store what they are passed and often the servers use UTC as a standard).
As I found nearly 200 instances of the mysql function NOW() used in the modules I decided I wanted to store the datetime fields as actual local UK time but I didn't want to alter every occurrence of NOW() in the code.
I also wanted to allow for any British Summer Time changes.
I couldn't set the database using TimeZone names as not all servers have this configured correctly so I needed to use the offset for it to always work.
So overall I decided to take the approach of
1. Setting the webserver/php timezone to the one I wanted (Europe/London in my case)
2. Calculate what Offset this TimeZone had from UTC/GMT
3. Use this Offset to set the Database to this TimeZone Offset
This is the way I did it though the code may well get improved by others!
There are timezone settings within PHP which are currently set in system/startup.php around line 50
This only sets a value using date_default_timezone_set if there is NO current setting so I changed it to always set my required TimeZone
Remove
Code: Select all
if (!ini_get('date.timezone')) {
date_default_timezone_set('UTC');
}
Code: Select all
date_default_timezone_set('Europe/London');
In vqmod\vqmod.php
Find and delete
Code: Select all
date_default_timezone_set('UTC');
(I fdidn't use any configs as they are set much later in the process after the initial database connection)
After
Code: Select all
mysql_query("SET SQL_MODE = ''", $this->connection);
Code: Select all
// get local time on Web/PHP server
$localtime = strtotime(date('Y-m-d H:i:s'));
//get local time in GMT/UTC (i.e GMT/UTC is set as +0:00 on database and other timezones are set as +/- hours of this)
$gm_localtime = strtotime(gmdate('Y-m-d H:i:s'));
//find offset in hours (if any - which allows for Daylight Saving Time or British Summer Time (BST))
$diff_hours = ($localtime - $gm_localtime) / 3600;
//Then the Database server needs to be set to this Offset to store/retrieve values as local ones
$adjust = "SET time_zone = '";
if ($diff_hours > 0) {
$adjust .= "+" . ceil($diff_hours);
} elseif ($diff_hours < 0) {
$adjust .= floor($diff_hours);
} else {
$adjust .= "+0";
}
$adjust .= ":00'";
mysql_query($adjust, $this->connection);