Mysql: How to diagnosis and correct wrong timezone

Question

In my server, I have the following from the command line:

]$ date
Fri Sep 16 13:47:02 JST 2011

Which is correct.

in mysql, I have the following:

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2011-09-16 04:50:21 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.00 sec)

So, it is set to the system time, but then why is not showing the same time?

Possible duplicate of http://stackoverflow.com/questions/2934258/how-do-i-get-the-current-time-zone-of-mysql — Ashwin A, Sep 16 '11 at 05:05

score 3 · Answer 1 · answered Apr 01 '14 at 04:01

3

You just need to restart mysqld after altering timezone of System..

The Global time zone of MySQL takes timezone of System. When you change any such attribute of system, you just need a restart of Mysqld.

That's it.

answered Apr 01 '14 at 04:01

Storm Young

79
2

score 0 · Accepted Answer · edited May 23 '17 at 12:29

0

Your queries are returning the values in UTC but from the command line output your system is in JST. Since the variables are returning SYSTEM I would suggest that perhaps your configuration has identified system as UTC. Check out your my.cnf as suggested here How do I make MySQL's NOW() and CURDATE() functions use UTC?.

edited May 23 '17 at 12:29

Community

1
1

answered Sep 16 '11 at 05:07

Carth

2,303
1
17
26

Thanks for response. No timezone is specified in my.cnf. So, would it just be a matter of setting: [mysqld_safe] timezone = JST and restarting? – James John McGuire 'Jahmic' Sep 16 '11 at 05:10
You can change this at runtime as well. Check out the documentation here. http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html – Carth Sep 16 '11 at 05:22
If needed, I have root access, but not sure how to get SUPER priv in Mysql. – James John McGuire 'Jahmic' Sep 16 '11 at 05:58

Mysql: How to diagnosis and correct wrong timezone

2 Answers2