How can I learn current timezone information from MySQL?

Database
1

We are working with GMT timestamps in MySQL. But in some deployments, we got some timezone related problems. We don’t have any access to MySQL servers other than MySQL client connection. So, how I learn current timezone information from MySQL console?

2

You can see global time zone and time zone selected with your current MySQL session from @@global.time_zone and @@session.time_zone variables like below:

mysql> SELECT @@global.time_zone, @@session.time_zone, NOW();
+--------------------+---------------------+---------------------+
| @@global.time_zone | @@session.time_zone | NOW()               |
+--------------------+---------------------+---------------------+
| SYSTEM             | SYSTEM              | 2015-01-12 16:32:21 |
+--------------------+---------------------+---------------------+

According to above output, if you don’t have access the MySQL server, it is not clear whether time zone settings (SYSTEM) is correct in the server or not.

To be sure that you’re working in GMT mode or not, you have to calculate GMT time difference with UTC_TIMESTAMP function as below:

mysql> SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);
+--------------------------------+
| TIMEDIFF(NOW(), UTC_TIMESTAMP) |
+--------------------------------+
| 02:00:00                       |
+--------------------------------+

If you see a difference other than zero like above, you can think that server has also setup with time zone other than GMT.