cancel
Showing results for 
Search instead for 
Did you mean: 

Database server time zone error

wallregg
Dabbler
Posts: 23
Registered: ‎30-07-2007

Database server time zone error

In my database I have a table with a field that updates with the CURRENT_TIMESTAMP whenever a row is created or updated.  Recently, when retreived from the database, the times have all been five hours slow.
I've just noticed that under the 'Variables' tab in phpMyAdmin (on the 'sql5c51a.megasqlservers.eu' server) the value for 'system time zone' is EST, which would explain the five hour time difference.
Can this be changed (back?) to GMT?  Makes more sense to be on UK time rather than New York time!
8 REPLIES 8
Chris
Legend
Posts: 17,724
Thanks: 600
Fixes: 169
Registered: ‎05-04-2007

Re: Database server time zone error

Let me give someone here a nudge to see if that's possible.
Former Plusnet Staff member. Posts after 31st Jan 2020 are not on behalf of Plusnet.
bobpullen
Community Gaffer
Community Gaffer
Posts: 16,869
Thanks: 4,950
Fixes: 315
Registered: ‎04-04-2007

Re: Database server time zone error

I suspect it's always been like this. Hostopia have a shared platform and we're not the only provider using it. Based on this, I doubt they'd change the timezone to suit us, especially considering it would probably be to the detriment of others.
What's being used to retrieve these times from the database? PHP? If so, then could you not define your timezone using an .htaccess file like this?
php_value date.timezone Europe/London

Bob Pullen
Plusnet Product Team
If I've been helpful then please give thanks ⤵

Townman
Superuser
Superuser
Posts: 22,923
Thanks: 9,542
Fixes: 159
Registered: ‎22-08-2007

Re: Database server time zone error

Bob,
One could argue that the current-time stamp generated within the database should be in UTC (Zulu time) not local time.
...though think before posting, it is possible that they are, but are being changed to local time by the extraction layer - managing date-time stamps across time zones always was a pain in the bum!
Kevin

Superusers are not staff, but they do have a direct line of communication into the business in order to raise issues, concerns and feedback from the community.

bobpullen
Community Gaffer
Community Gaffer
Posts: 16,869
Thanks: 4,950
Fixes: 315
Registered: ‎04-04-2007

Re: Database server time zone error

Quote from: Townman
... it is possible that they are, but are being changed to local time by the extraction layer...

I suspect you're right Kevin.

Bob Pullen
Plusnet Product Team
If I've been helpful then please give thanks ⤵

wallregg
Dabbler
Posts: 23
Registered: ‎30-07-2007

Re: Database server time zone error

Hello Bob,
Yes, PHP.  I'm just displaying the data the server sends out, in the format '2014-11-24 10:51:31' for example.  From my database backups it is stored as GMT but in a page and in phpMyAdmin it is EST. 
I'm certain that until about 1.5 to 2 weeks ago it was showing the correct time, so can only assume that something changed recently. 
bobpullen
Community Gaffer
Community Gaffer
Posts: 16,869
Thanks: 4,950
Fixes: 315
Registered: ‎04-04-2007

Re: Database server time zone error

If there are any other reports to support that this has recently changed then I'll raise it with Hostopia. In the interim, I suspect the .htaccess route will do the trick.

Bob Pullen
Plusnet Product Team
If I've been helpful then please give thanks ⤵

wallregg
Dabbler
Posts: 23
Registered: ‎30-07-2007

Re: Database server time zone error

Having spent an hour or so playing around with timezones and .htaccess files with no success, I deleted the table and reloaded it from a backup.  Times now showing correctly !?!?!  Not sure why. 
Am confused but happy  Smiley
Maybe 'system time zone' = EST and the five hour difference in my data is just a coincidence.  Appologies to all if this has been a wild-goose chase!
Townman
Superuser
Superuser
Posts: 22,923
Thanks: 9,542
Fixes: 159
Registered: ‎22-08-2007

Re: Database server time zone error

Hi,
Time zones, DST and databases have always been a pain in the bum.  I recall working on a major UK institution's system using oracle (yes it hits the big boys too) where we often saw a 1 hour difference in database time stamps depending on how the database query was run - direct into Oracle or through the application middle ware.
I do not recall that we ever really resolved the issue, other than by ensuring we never mixed data query strategies.  It can make diagnostic analysis very difficult!  Cheesy
And whilst I'm sharing pearls of wisdom - in respect of working with across the pond (time zone) platforms - keep all dates in ISO format  - CCYYMMDD - or DD MMM CCYY so as to avoid 5th Jan and 1st May confusion.  Applications written / tested on date data before the 13th of the month always worked better.  Grin

Superusers are not staff, but they do have a direct line of communication into the business in order to raise issues, concerns and feedback from the community.