Bug #9518 TIMEZONE variable should be changed according to daylight savings
Submitted: 31 Mar 2005 10:39 Modified: 22 Feb 2006 11:32
Reporter: Anders Henke Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: General Severity:S4 (Feature request)
Version:4.1.10 and 4.0.24, 4.1, 5.0, 5.1, 6.0 bzr OS:Any (Any)
Assigned to: CPU Architecture:Any

[31 Mar 2005 10:39] Anders Henke
Easter weekend, we had a switch in our daylight savings time from CET to CEST.
During this, I noticed that servers started before the switch still had "CET" in the output of "show variables" and only servers started after the switch gave out "CEST".

The documentation correctly identifies this in 5.2.3 Server System Variables:
The server system time zone. When the server begins executing, it inherits a time zone setting from the machine defaults, possibly modified by the environment of the account used for running the server or the startup script. The value is used to set system_time_zone. Typically the time zone is specified by the TZ environment variable. It also can be specified using the --timezone option of the mysqld_safe script. This variable was added in MySQL 4.1.3.

So to me, this isn't a bug - yet some customers as well as support staff believe that for accuracy, this variable should represent the current value. As servers with long
uptime do correctly calculate dates and times with CEST, but state "time_zone=CET",
some people do have the crazy idea that the server prints a "wrong" date and they 
have to manually recalculate the given results.

Personally I see this as merely a cosmetic feature, as the documentation is specific 
enough (to me) and the issue doesn't directly affect the accuracy MySQL calculates or 
presents any dates, but a few other ones have a different opinion on that third ones do assume that time_zone (4.0) and  system_time_zone (4.1) represent the current timezone the server is currently using rather than the timezone used at server startup.

How to repeat:

A 4.1.10 server with 35 days of uptime:
| system_time_zone                | CET                                      |
| time_format                     | %H:%i:%s                                 |
| time_zone                       | SYSTEM                                   |

A 4.0.24 server with 2 days of uptime:
| timezone                        | CEST                                                                                                                                                                                                              |

A 4.0.24 server with 35 days of uptime:
| timezone                        | CET                                                                                                                                                                                                               |

Somehow strange is that the timezone value cannot be changed on a running server - the server prints out a "wrong" error message:

mysql> show variables like 'timezone';
| Variable_name | Value |
| timezone      | CET   |
1 row in set (0.00 sec)

mysql> set global timezone="CEST";
ERROR 1193: Unknown system variable 'timezone'

Suggested fix:
-Schedule some kind of job which "fixes" the timezone variable according to daylight savings.
-Or just print out the "current" timezone rather than the one used at startup upon "show variables".
-Or add a note to the documentation that the timezone/system_timezone-variable doesn't
 reflect the current active timezone but the base timezone used for date calculations and
 daylight savings are handled correctly, although this variable might let someone assume
 that they were not.

Changing the error message with a more meaningful one might also be a good idea,
e.g. "Variable '%-.64s' can only be read, not set.".
[22 Feb 2006 11:32] Valeriy Kravchuk
Thank you for a resonable feature request. I believe, that, at least, current behaviour should be clearly described in the manual.
[23 Oct 2008 5:54] Sveta Smirnova
Bug #40271 was marked as duplicate of this one.
[24 Oct 2008 2:38] Roberto Spadim
submitted at 31 Mar 2005 12:39 ???
couldn't resolve this?