5

So a month ago I had purchased a server from a hosting company.

The info are;

Linux Centos (latest version).

I installed ZPanel which installs phpMyAdmin, MySQL and all the other good stuff. Anyway, I have managed to change my SERVER time to my local time which is GMT. However when I try to set it on my MySQL server it shows the right time, but when I use the

now()

function in PHP, the time stores +1 hour ahead of my current time, which pretty much messes up my system.

Does anyone know how to fix this? Please It's been a few weeks, yet nothing to fix it :/

Thank you.

2
  • 2
    What responses do you get for this query? SELECT @@SYSTEM_TIME_ZONE, @@TIME_ZONE, NOW(); (and you might mention whether or not this is the correct time, based on when you run this query manually). – Michael - sqlbot Aug 4 '13 at 19:32
  • @Michael-sqlbot Apologies for the extremely late reply. From the query you told me to do, I got: @@SYSTEM_TIME_ZONE | @@TIME_ZONE | NOW() UTC | SYSTEM | 2013-08-11 16:41:52 I live in the UK, and my PHP time is Europe/England. That's the time I would like it to be. The time is also wrong, it should be 5:41 PM – Dharmesh Aug 11 '13 at 16:43
5

I had this exact same problem after I had updated my server time. I just had to restart the MySql server then it reset the time to match my server time.

/etc/init.d/mysqld restart

Reference - https://major.io/2007/07/01/mysql-time-zone-different-from-system-time-zone/

1
  • 1
    A common alternative: sudo service mysql restart – Peter Jan 7 '16 at 12:48
0

In my case my.cnf had wrong setting for timezone, correct format is like this:

default_time_zone=Europe/Riga

Also make sure you have updated mysql timezones via shell like this: https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

using mysql_tzinfo_to_sql ...

0

If you can't get the server and SQL server in sync, in PHP, you could always use this workaround...

$date = date('D jS M Y, G:i:s a', strtotime('-1 hour'));

instead of now();

If you used shared hosting, you can often contact the support to change the default time zone for you.. I had to do that with ixwebhosting and they did it for me no problems.

This is complicated though because MySQL stores 'datetime' data type fields internally as UTC, but PhpMyAdmin shows you the dates using the server default time.. This can often cause confusion.

You can try adding this line before your SQL statement in PhpMyAdmin:

SET @@session.time_zone='+00:00'; SELECT * FROM TableName

or

SET time_zone = '+00:00'; SELECT now();

If you have SUPER privelege you can also set the system time by using the following..

SET GLOBAL time_zone = '+00:00';

You can see more here: http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html.

1
  • MySQL does not store DATETIME values internally as UTC and does not do implicit conversions on them, either. It stores them as entered within the range constraints of the data type. You are thinking of the TIMESTAMP data type. Hacking the time in php is also not a good piece of advice, neither is coercing the global time zone in MySQL without first setting the underlying OS time zone correctly, which is probably what's happening here. – Michael - sqlbot Aug 4 '13 at 23:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.