MySQL Handling Time Zones What is my server's local time zone setting?
Example
Each server has a default global time_zone setting, configured by the owner of the server machine. You can find out the current time zone setting this way:
SELECT @@time_zone
Unfortunately, that usually yields the value SYSTEM, meaning the MySQL time is governed by the server OS's time zone setting.
This sequence of queries (yes, it's a hack) gives you back the offset in minutes between the server's time zone setting and UTC.
CREATE TEMPORARY TABLE times (dt DATETIME, ts TIMESTAMP);
SET time_zone = 'UTC';
INSERT INTO times VALUES(NOW(), NOW());
SET time_zone = 'SYSTEM';
SELECT dt, ts, TIMESTAMPDIFF(MINUTE, dt, ts)offset FROM times;
DROP TEMPORARY TABLE times;
How does this work? The two columns in the temporary table with different data types is the clue. DATETIME data types are always stored in local time in tables, and TIMESTAMPs in UTC. So the INSERT statement, performed when the time_zone is set to UTC, stores two identical date / time values.
Then, the SELECT statement, is done when the time_zone is set to server local time. TIMESTAMPs are always translated from their stored UTC form to local time in SELECT statements. DATETIMEs are not. So the TIMESTAMPDIFF(MINUTE...) operation computes the difference between local and universal time.
