MySQL records timestamps changed

Question

I changed my server time using this command

cp /usr/share/zoneinfo/US/central etc/localtime

Ever since I changed the server time. All my MySQL records have changed the timestamp fields 6 hours ahead. I tried changing the time back and the records won't update. Any ideas?

Web Server OS is CentOS

Did you restart mysqld after changing the timezone file back? — Barmar, Nov 24 '14 at 17:09

score 4 · Answer 1 · edited May 23 '17 at 11:59

4

the timestamp didn't changed. The problem is that the database still with the old timezone (which by the time difference I suppose was UTC). The get values you expect you should change also your database timezone.

Here is better explained how to do that: How do I set the time zone of MySQL?

But basically you can change it forever (global), with this command:

SET GLOBAL time_zone = 'America/New_York';

Or for each session with this other:

SET time_zone = 'America/New_York';

edited May 23 '17 at 11:59

Community

1
1

answered Nov 24 '14 at 17:15

manuelmhtr

326
2
5

Thank you for the response. I am going to try this now :D – Rafael Nov 24 '14 at 17:27
does it really change it `forever`? Will not next `restart` read old value from configs? – vp_arth Nov 25 '14 at 08:28

score 1 · Accepted Answer · answered Nov 25 '14 at 07:31

1

The timestamp is a session variable in MySQL. It reflects the value returned by now(). For more info read: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_time_zone

You can specify the default time zone in your MySQL my.cnf configuration file as:

default_time_zone=America/New_York

and restart your MySQL service as:

sudo service mysqld restart

answered Nov 25 '14 at 07:31

sid1408

404
3
10

Thank you I will try working with this tomorrow. Very insightful thank you – Rafael Nov 26 '14 at 09:02

MySQL records timestamps changed

2 Answers2