How do I get the current time zone of MySQL?

Question

Anyone knows if there is such a function in MySQL?

UPDATE

This doesn't output any valid info:

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+

Or maybe MySQL itself can't know exactly the time_zone used,that's fine, we can involve PHP here, as long as I can get valid info not like SYSTEM...

Answer 1

SELECT EXTRACT(HOUR FROM (TIMEDIFF(NOW(), UTC_TIMESTAMP))) AS `timezone`

This will return the timezone as an integer (eg: -6), handling positive or negative times (here is where EXTRACT comes into play: HOUR function alone returns negative timezones as positive).

Answer 2

Check out MySQL Server Time Zone Support and the system_time_zone system variable. Does that help?

Answer 3

Simply SELECT @@system_time_zone;

Returns PST (or whatever is relevant to your system).

If you're trying to determine the session timezone you can use this query:
SELECT IF(@@session.time_zone = 'SYSTEM', @@system_time_zone, @@session.time_zone);

Which will return the session timezone if it differs from the system timezone.

Answer 4

It may be as stupid as this

select timediff(current_time(),utc_time())

as is whole mysql

you won't get directly timezone value this way, but if there were no other way...

@@global.time_zone cannot be used in view as it is a variable - and it returns quite unusable value 'SYSTEM' ( i haven't got why somebody bothered with it )

if you need to use your query in a session with changed time_zone ( by session SET TIME_ZONE = ) you will get that with @@session.time_zone if you query @@global.time_zone you get 'SYSTEM' catch 22

if you try datediff, date_sub, or timediff with now() and utc_time() you'll probably run into conversion issues being silently chown by a server

The worst documentation i have ever seen my not help you either.

Excellent work, everybody!

But the something suggested above will probably work at least with some server versions as is mine (5.5.43-37) hosted solution.