Bug #49621 time settings in GB timezone is incorrect even though system_time_zone says GMT
Submitted: 11 Dec 2009 14:33 Modified: 14 Dec 2009 11:36
Reporter: Geoff Winkless Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0.88, 5.0.85, 5.0.15 OS:Any
Assigned to: CPU Architecture:Any

[11 Dec 2009 14:33] Geoff Winkless
Description:
If you do not explicitly set the timezone using env('TZ') or with a startup option to mysql, it seems to work out the system timezone for itself

However, at least for GMT, it then seems to get this wrong: even though SYSTEM_TIME_ZONE returns GMT, UNIX_TIMESTAMP returns values as if the offset is 3600.

mysql> select @@session.time_zone;
+---------------------+
| @@session.time_zone |
+---------------------+
| SYSTEM              |
+---------------------+
1 row in set (0.00 sec)
mysql> show variables like '%time%zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | GMT    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)
mysql> select 86400-unix_timestamp("1970-01-02 00:00:00");
+---------------------------------------------+
| 86400-unix_timestamp("1970-01-02 00:00:00") |
+---------------------------------------------+
|                                        3600 |
+---------------------------------------------+
1 row in set (0.00 sec)
mysql> Bye
[root@geofftest ~]# date
Fri Dec 11 14:14:10 GMT 2009

Note that setting the session timezone explicitly to +00:00 returns the correct value:

mysql> set @@SESSION.TIME_ZONE='+00:00';
Query OK, 0 rows affected (0.01 sec)

mysql> select 86400-unix_timestamp("1970-01-02 00:00:00");
+---------------------------------------------+
| 86400-unix_timestamp("1970-01-02 00:00:00") |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+
1 row in set (0.00 sec)

Forcing the TZ variable to "GMT" before starting mysql also produces the expected result.

[root@geofftest ~]# export TZ='GMT';
[root@geofftest ~]# /etc/init.d/mysqld start
Starting MySQL:                                            [  OK  ]
[root@geofftest ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.85 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT @@SESSION.TIME_ZONE;
+---------------------+
| @@SESSION.TIME_ZONE |
+---------------------+
| SYSTEM              |
+---------------------+
1 row in set (0.00 sec)

mysql> show variables like '%time%zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | GMT    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> select 86400-unix_timestamp("1970-01-02 00:00:00");
+---------------------------------------------+
| 86400-unix_timestamp("1970-01-02 00:00:00") |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+
1 row in set (0.00 sec)

How to repeat:
Set up server in GMT. Unset TZ env variable and restart mysql. Check offset used by UNIX_TIMESTAMP as above. Then set TZ explicitly to GMT and restart mysql. Note offset has changed, even though the system timezone variables have not.
[11 Dec 2009 17:26] Geoff Winkless
(updated to version 5.0.88 and checked problem still exists)
[13 Dec 2009 7:34] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior.

Please send us result of query SHOW GLOBAL VARIABLES LIKE '%time%zone%';
[14 Dec 2009 10:40] Geoff Winkless
Apologies: I had assumed the server was set up with GMT locale but when I just checked it is actually set up with GB.

OS is CentOS 4.4 but also fails on RHEL5.3, on an old legacy RH9 server and on a Solaris 9 box. It's possible there's a problem with the GB zoneinfo file (I assume both Solaris and RH use the same Olsen source?) but why does mysql tell me that the setting is GMT when if it were GMT the time would be correct?

[root@geofftest etc]# rm /etc/localtime
rm: remove regular file `/etc/localtime'? y
[root@geofftest etc]# ln -s /usr/share/zoneinfo/GMT /etc/localtime
[root@geofftest etc]# /etc/init.d/mysqld stop
Stopping MySQL:                                            [  OK  ]
[root@geofftest etc]# /etc/init.d/mysqld start
Starting MySQL:                                            [  OK  ]
[root@geofftest etc]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.88 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select 86400-unix_timestamp("1970-01-02 00:00:00");
+---------------------------------------------+
| 86400-unix_timestamp("1970-01-02 00:00:00") |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> Bye
[root@geofftest etc]# rm /etc/localtime
rm: remove symbolic link `/etc/localtime'? y
[root@geofftest etc]# ln -s /usr/share/zoneinfo/GB /etc/localtime
[root@geofftest etc]# /etc/init.d/mysqld stop
Stopping MySQL:                                            [  OK  ]
[root@geofftest etc]# /etc/init.d/mysqld start
Starting MySQL:                                            [  OK  ]
[root@geofftest etc]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.88 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select 86400-unix_timestamp("1970-01-02 00:00:00");
+---------------------------------------------+
| 86400-unix_timestamp("1970-01-02 00:00:00") |
+---------------------------------------------+
|                                        3600 |
+---------------------------------------------+
1 row in set (0.01 sec)

mysql> show global variables like '%time%zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | GMT    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)
[14 Dec 2009 10:47] Geoff Winkless
Modified synopsis because it turns out that the reason setting TZ explicitly appeared to work was because I was actually forcing the server to GMT locale not GB...

To be clear, explicitly setting $TZ to GB produces the same incorrect value.

[root@geofftest etc]# export TZ=GB
[root@geofftest etc]# /etc/init.d/mysqld start
Starting MySQL:                                            [  OK  ]
[root@geofftest etc]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.88 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select 86400-unix_timestamp("1970-01-02 00:00:00");
+---------------------------------------------+
| 86400-unix_timestamp("1970-01-02 00:00:00") |
+---------------------------------------------+
|                                        3600 |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> show global variables like '%time%zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | GMT    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> Bye
[14 Dec 2009 11:03] Sveta Smirnova
Thank you for the feedback.

Please send us result of queries 

show status like 'uptime
and
select now();
[14 Dec 2009 11:07] Geoff Winkless
Thanks for the speedy response!

mysql> show status like 'uptime%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 1265  |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2009-12-14 10:48:12 |
+---------------------+
1 row in set (0.00 sec)
[14 Dec 2009 11:36] Geoff Winkless
Discovered my own reason.

The UK stupidly was in "British Summer Time" permanently between 1968 and 1971. As such in "GB" locale 1970-01-02 00:00:00 is indeed 82800.

Sorry to have wasted your time and thanks for your efforts.