How do I get the current time zone of MySQL? Ask Question

From the manual (section 9.6):

The current values of the global and client-specific time zones can be retrieved like this:
mysql> SELECT @@global.time_zone, @@session.time_zone;

Edit The above returns SYSTEM if MySQL is set to use the system's timezone, which is less than helpful. Since you're using PHP, if the answer from MySQL is SYSTEM, you can then ask the system what timezone it's using via date_default_timezone_get. (Of course, as VolkerK pointed out, PHP may be running on a different server, but as assumptions go, assuming the web server and the DB server it's talking to are set to [if not actually in] the same timezone isn't a huge leap.) But beware that (as with MySQL), you can set the timezone that PHP uses (date_default_timezone_set), which means it may report a different value than the OS is using. If you're in control of the PHP code, you should know whether you're doing that and be okay.

But the whole question of what timezone the MySQL server is using may be a tangent, because asking the server what timezone it's in tells you absolutely nothing about the data in the database. Read on for details:

Further discussion:

If you're in control of the server, of course you can ensure that the timezone is a known quantity. If you're not in control of the server, you can set the timezone used by your connection like this:

set time_zone = '+00:00';

That sets the timezone to GMT, so that any further operations (like now()) will use GMT.

Note, though, that time and date values are not stored with timezone information in MySQL:

mysql> create table foo (tstamp datetime) Engine=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into foo (tstamp) values (now());
Query OK, 1 row affected (0.00 sec)

mysql> set time_zone = '+01:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select tstamp from foo;
+---------------------+
| tstamp              |
+---------------------+
| 2010-05-29 08:31:59 |
+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone = '+02:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select tstamp from foo;
+---------------------+
| tstamp              |
+---------------------+
| 2010-05-29 08:31:59 |      <== Note, no change!
+---------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2010-05-29 10:32:32 |
+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone = '+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2010-05-29 08:32:38 |      <== Note, it changed!
+---------------------+
1 row in set (0.00 sec)