Mysql: How to diagnosis and correct wrong timezone

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?

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.

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