MySQL UTC_TIMESTAMP() ignoring current @@time_zone setting

Question

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?

Answer 1

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.