Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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

2 Answers 2

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/

share|improve this answer

In PHP, you could always use...

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

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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