MySQL UTC_TIMESTAMP() ignoring current @@time_zone setting
I have two Linux/MySQL servers located in the UK, current system timezone on both reports BST (GMT+1) and yet I have found a discrepency in MySQL's output.
The following query:
SELECT version(), @@time_zone, @@system_time_zone, NOW(), UTC_TIMESTAMP()
returns:
开发者_开发问答Server A: 5.0.27-community-nt | SYSTEM | GMT | 2010-10-12 12:17:01 | 2010-10-12 11:17:01
Server B: 5.0.45-log | SYSTEM | GMT Daylight Time | 2010-10-12 12:17:51 | 2010-10-12 11:17:51
So, server A reports it is set to GMT. The server process was started on 1st March when GMT was in effect, so I expected this. However, the UTC_TIMESTAMP() has correctly (but unexpectedly) reported UTC being 1 hour before localtime.
On server B, the MySQL process was started during the summer, so it correctly reports GMT Daylight Time, and again correctly reports UTC an hour earlier.
My question is, how did server A get the "right" answer? And, will it still be right on October 31st when the localtime reverts to GMT+0?
I think what happens is that when you start the MySQL server, it populates the @@system_time_zone
variable, but even when it changes (eg. due to DST), it's not reflected in the variable. However, although the @@system_time_zone
says "GMT", when the MySQL server evaluates the current date, and @@time_zone
is system, it asks the system for the date and the DST affects that, no matter what system_time_zone
variable says. So basically the only "issue" here is that system_timezone
variable does not change automatically, even if the system's timezone changes.
精彩评论