CODEWITHSUNDEEP
phpmysqlapachetimezoneserver
Eduardo Ponce de Leon
Eduardo Ponce de Leon

Reputation: 9706

Update mysql timezone and change all TIMESTAMPS columns

First of all I have MYSQL set to America/Montreal 

SET time_zone = 'America/Montreal

to make sure it all works fine I use in PHP:

date_default_timezone_set('America/Montreal');

Something I don't understand is that the values saved on the database are incorrect, for example 11:00:00 will save 10:00:00, but when fetching the information I get 11:00:00 displayed. So at the end of the day the system works as we want but the data on the database is inaccurate. Can someone explain me why? For example I have a column start_time set as TIMESTAMP

"INSERT into table set start_time = '".date("Y-m-d H:i:s")."'

Lets say the current time is 11:00:00, but the time saved will be 10:00:00 and when fetched it will return 11:00:00.

Then coming back to my question, since the data is inaccurate I want to change the server time to the right one and have MySQL use the new time, if I do so will the date / time data in the database get updated automatically? If not how I can I update all my dates and times for the new time zone?

Thanks.

Upvotes: 3

Views: 2417

Answers (1)

Majid
Majid

Reputation: 3481

That's because your MySQL server has another timezone (Your PHP server is different than your MySQL server)! When you fetch a timestamp field, php automatically localize it based on your default timezone. To have the correct timestamp on your database too, you need to set it when you have connected to MySQL:

$mysqli->query("set time_zone = '+00:00'");

This command set the your MySQL connection timezone to GMT, but for Montreal it should be -05:00.

hope this helps!

Upvotes: 1

Related Questions