Post by keithb » Thu May 12, 2011 2:22 am

Sometimes in OpenCart the effect of different timezones can affect what is stored in the database and displayed to the user.

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');
}
Replace with (or the required timezone)

Code: Select all

date_default_timezone_set('Europe/London');
I also have QPhoria's excellent VQMod and I found there was a similar line there
In vqmod\vqmod.php
Find and delete

Code: Select all

date_default_timezone_set('UTC');
The main code logic went into system/database/mysql.php so is only called once per page.
(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);
Add

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);
This solved my problem but it depends on your hosting as to whether you need or want to use it or not. :-\

MY CAR BULBS


New member

Posts

Joined
Thu Jun 03, 2010 6:12 pm


Post by spitos » Fri May 27, 2011 12:27 am

Genius!

I've spent so long looking for this solution! Thank You :clap:

Image
Google Analytics Expert - Advanced e-commerce tracking, Product & options reporting, transaction/conversion reporting, Google Adwords conversion & profit reporting, goal & funnel reporting, event tracking, site search tracking, multi-store compatibility, EU Cookie Law compliance and works with any theme or checkout! Easy vqmod install. Get it here


User avatar
Active Member

Posts

Joined
Mon May 23, 2011 6:19 pm
Location - UK

Post by keithb » Fri May 27, 2011 4:29 pm

Glad it helped you ;D

MY CAR BULBS


New member

Posts

Joined
Thu Jun 03, 2010 6:12 pm


Post by AvinThat » Wed Jun 01, 2011 2:41 pm

100% win.

nice, been reading many a thread about this, none solved it.

your solution seems to work!

cheers,

happy bunny

The Gift Shack | Zippo Lighters | Zippo Blu Lighters


Newbie

Posts

Joined
Thu Aug 19, 2010 12:59 am

Post by MatthewC » Fri Sep 02, 2011 3:33 pm

Would I have to change the code that I insert into mysql.php for Sydney, Australia?

If so what would the code look like..? Im just a little bit confused as to how I would modify this code to set OpenCart to Sydney time..

We are GMT +10 here in Sydney

My server is located in San Francisco, so I have been have much trouble to get the opencart time set to my local Sydney time. :'(

Much Thanks!

Newbie

Posts

Joined
Tue Aug 23, 2011 7:17 pm

Post by keithb » Sat Sep 03, 2011 12:35 am

Just enter the required timezone for the webserver (step 1)
ie in system/startup.php around line 50 change

Code: Select all

if (!ini_get('date.timezone')) {
     date_default_timezone_set('UTC');
}

to

Code: Select all

date_default_timezone_set('Australia/Sydney');

Steps 2 and 3 are handled by the code

MY CAR BULBS


New member

Posts

Joined
Thu Jun 03, 2010 6:12 pm


Post by dealstation » Sun Oct 16, 2011 3:18 am

how can i edit the file to pm or am time?

thnx

New member

Posts

Joined
Tue Aug 16, 2011 7:59 am

Post by keithb » Mon Oct 17, 2011 8:57 pm

My approach just does the necessary conversion to the 'wanted' date and time
You appear to want to just change this date to a different 'display' style
I wanted to display date AND time of an Order so have done this by defining an appropriate constant in the /admin/language/english/english.php file

Find

Code: Select all

$_['date_format_short']       = 'd/m/Y';
$_['date_format_long']        = 'l dS F Y';
And add after
(for this example of displaying time - you would need to define it different for your use)

Code: Select all

$_['date_format_time']        = 'd/m/Y - D  H:i';
Then wherever the usual code is used
eg

Code: Select all

'date_added'       => date($this->language->get('date_format_short'), strtotime($order_info['date_added'])),
I just replace with

Code: Select all

'date_added'       => date($this->language->get('date_format_time'), strtotime($order_info['date_added'])),
It just needs changing in places that you want this particular format (not necessarily everywhere)

MY CAR BULBS


New member

Posts

Joined
Thu Jun 03, 2010 6:12 pm


Post by joy » Mon Oct 17, 2011 10:31 pm

keithb wrote: This solved my problem but it depends on your hosting as to whether you need or want to use it or not. :-\
Thank you thank you thank you...
This is very useful to me!

Although I was trying to find a way not to record my local time in the database, but after searching high and low on the internet, I think this is the easier solution unless I can get to set the server timezone. :D

OC version 1.5.1.3


joy
New member

Posts

Joined
Fri Oct 14, 2011 9:18 am

Post by amboss » Sun Jan 29, 2012 8:15 am

keithb wrote:This solved my problem but it depends on your hosting as to whether you need or want to use it or not. :-\
It took me several hours to see that orders are using the db's timezone. I have solved writing static time difference in mysql.php first then I saw your nicely written solution covering both php timezone and db timezone, thanks a lot.

emreyuce.com | datekiss.com | tamsepetlik.com | undobilisim.com


Newbie

Posts

Joined
Sat Aug 27, 2011 8:05 pm

Post by MrTech » Sat Mar 10, 2012 11:43 am

I just wanted to chime in to say that only a small change is needed to get this working in v1.5.2.1

when working on the 'mysql.php' database step, you'll need to change:

From: $this->connection);
To: $this->link);

In other words, search for the word 'link' instead of 'connection'. Then when you paste the code into the file, make sure to also replace 'connection' with 'link' in there as well.

Thanks for sharing!

~
Install Extensions OR OpenCart Fast Service! PayPal Accepted
I will professionally install and configure any free or purchased theme, module or extension.

Visit http://www.mrtech.ca if you need an OpenCart webmaster
~


User avatar
Active Member

Posts

Joined
Mon Jan 09, 2012 2:39 pm
Location - Canada, Eh!

Post by MatthewC » Thu Jun 14, 2012 10:30 am

Thanks for that update MrTech, much appreciated!

Newbie

Posts

Joined
Tue Aug 23, 2011 7:17 pm

Post by tolinho » Thu Aug 09, 2012 12:40 am

keithb wrote:My approach just does the necessary conversion to the 'wanted' date and time
You appear to want to just change this date to a different 'display' style
I wanted to display date AND time of an Order so have done this by defining an appropriate constant in the /admin/language/english/english.php file

Find

Code: Select all

$_['date_format_short']       = 'd/m/Y';
$_['date_format_long']        = 'l dS F Y';
And add after
(for this example of displaying time - you would need to define it different for your use)

Code: Select all

$_['date_format_time']        = 'd/m/Y - D  H:i';
Then wherever the usual code is used
eg

Code: Select all

'date_added'       => date($this->language->get('date_format_short'), strtotime($order_info['date_added'])),
I just replace with

Code: Select all

'date_added'       => date($this->language->get('date_format_time'), strtotime($order_info['date_added'])),
It just needs changing in places that you want this particular format (not necessarily everywhere)
Hi.
I just did this changes on my cart Version 1.5.3.1

All is perfect!

I did a little change
In this line
$_['date_format_time'] = 'd/m/Y - D H:i';

I used
$_['date_format_time'] = 'd/m/Y H:i:s';

Thanks for sharing your knowledge :)

New member

Posts

Joined
Sat Jul 21, 2012 2:01 am

Post by sopedro » Thu Aug 23, 2012 11:11 pm

Freaking Genius...It works....i searched a lot for this and you save my life....Thanks

Newbie

Posts

Joined
Sat Jan 21, 2012 5:38 pm

Post by philbydevil » Wed Oct 10, 2012 5:13 pm

Has anyone got this working on 1.5.4.1?

MrTech's update is relevant, but the time's in admin haven't changed.... No errors either

I heart cmd-f, cmd-c, cmd-v, cmd-z + vQmod.
My favourite page...
v1.5.4.1


User avatar
Active Member

Posts

Joined
Fri Dec 03, 2010 5:20 am

Post by philbydevil » Wed Oct 10, 2012 9:47 pm

Ok, so it works on 1.5.4.1, but I couldn't get it to work via a vQmod... no luck changing startup.php and mysql.php, had to modify the files manually for some reason.

I heart cmd-f, cmd-c, cmd-v, cmd-z + vQmod.
My favourite page...
v1.5.4.1


User avatar
Active Member

Posts

Joined
Fri Dec 03, 2010 5:20 am

Post by liewzy » Mon Oct 15, 2012 1:06 am

In OpenCart v1.5.4.1, I modified last line code to make it work for my store.

from:

Code: Select all

mysql_query($adjust, $this->connection);
to:

Code: Select all

mysql_query($adjust, $this->link);

Newbie

Posts

Joined
Wed Aug 08, 2012 1:02 pm

Post by zekin » Mon Nov 26, 2012 9:43 am

liewzy wrote:In OpenCart v1.5.4.1, I modified last line code to make it work for my store.

from:

Code: Select all

mysql_query($adjust, $this->connection);
to:

Code: Select all

mysql_query($adjust, $this->link);
confirming this worked in 1.5.4.1

and thanks to the author, this is a one of the must-have mods.

My OC sites:
Top Choice Variety
Tiny World


New member

Posts

Joined
Wed Nov 21, 2012 8:13 pm

Post by Snufkin » Sun Dec 09, 2012 6:28 am

I put this correction to VQMOD file.
So it's much easy to use it in that way.
It's work for me in OC 1.5.4.1

[Updated 12.12] - Sorry there is problem in changing system/database/mysql.php part.
I don't know why, so, file is broken.
You still need to hardcode all changes :(
Last edited by Snufkin on Wed Dec 12, 2012 2:42 pm, edited 1 time in total.

Newbie

Posts

Joined
Tue Jun 08, 2010 1:10 am

Post by gurubob » Wed Dec 12, 2012 11:35 am

Great idea to wrap this up into a vqmod - I tried dropping it into my xml folder and it's not picking it up - all my other mods work fine. Tried clearing the vqcache folder and looked at the vqmod.log file for messages but nothing. Any idea why that would be?

User avatar
Newbie

Posts

Joined
Fri May 21, 2010 7:09 pm
Location - Dunedin, NZ
Who is online

Users browsing this forum: No registered users and 134 guests