Client Portal

MySQL timezone

Hello Chris, System timezone is fixed for shared server. It must be so because we cannot match each an every client preference when the parameter can only have single value. Client can still use different timezones but in their sessions. Consult per-connection time zone section in http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html Example 1 (when you are connecting with a client on the same machine and thus the same timezone as server) mysql> select timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')); +----------------------------------------------------------------+ | timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')) | +----------------------------------------------------------------+ | -06:00:00 | +----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP); +--------------------------------+ | TIMEDIFF(NOW(), UTC_TIMESTAMP) | +--------------------------------+ | -06:00:00 | +--------------------------------+ 1 row in set (0.00 sec) mysql> SET time_zone = '+3:00'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP); +--------------------------------+ | TIMEDIFF(NOW(), UTC_TIMESTAMP) | +--------------------------------+ | 03:00:00 | +--------------------------------+ 1 row in set (0.00 sec) mysql> SELECT @@global.time_zone, @@session.time_zone; +--------------------+---------------------+ | @@global.time_zone | @@session.time_zone | +--------------------+---------------------+ | SYSTEM | +03:00 | +--------------------+---------------------+ 1 row in set (0.14 sec) Do I need to worry? If your client is in India and he saves his local time say 10:30 PM (IST) then it will be saved as 11:00 AM server time (CST). Server time is GMT-6, IST is GMT+5:30 so it gives 11:30 hours of difference (10:30 PM - 11:10 = 11 AM). Anytime later when your client connects from India and run SELECT query, he will get 10:30PM as the time will be converted to his timezone. This is assuming his client software explicitly or implicitly sets session timezone to IST (GMT+5:30). Note that custom timezones can also be set for PHP, shell and JVM (Java). See also http://www.jvmhost.com/articles/java-and-timezones