Bug #66154 CONVERT_TZ using Etc/GMT zones gives wrong result
Submitted: 2 Aug 2012 9:21 Modified: 2 Aug 2012 9:33
Reporter: Peter Reinhold Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.0, 5.5, 5.5.28 OS:Any
Assigned to: CPU Architecture:Any
Tags: convert_tz timezone conversion
Triage: Needs Triage: D2 (Serious)

[2 Aug 2012 9:21] Peter Reinhold
Description:
I've imported the timezones as per http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html.

But, when I am using the Etc/GMT+? timezones to convert, the values that are returned are wrong.

For instance

SELECT CONVERT_TZ('2012-01-01 00:00:00', 'Etc/GMT+0', 'Etc/GMT+1')

returns 2011-12-31 23:00:00 when it should return 2012-01-01 01:00:00

Doing

SELECT CONVERT_TZ('2012-01-01 00:00:00', 'Europe/London', 'Europe/Copenhagen')

which should be the same query, returns the correct result.

This has been tested on Gentoo and CentOS, using MySQL 5.0 and 5.5 with the same results.

How to repeat:
Perform the following SQL

SELECT CONVERT_TZ('2012-01-01 00:00:00', 'Etc/GMT+0', 'Etc/GMT+1')
[2 Aug 2012 9:33] Valeriy Kravchuk
Verified with recent mysql-5.5 on Linux FC14:

[openxs@chief 5.5]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.28-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> SELECT CONVERT_TZ('2012-01-01 00:00:00', 'Etc/GMT+0', 'Etc/GMT+1')
    -> ;
+-------------------------------------------------------------+
| CONVERT_TZ('2012-01-01 00:00:00', 'Etc/GMT+0', 'Etc/GMT+1') |
+-------------------------------------------------------------+
| 2011-12-31 23:00:00                                         |
+-------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT CONVERT_TZ('2012-01-01 00:00:00', 'Europe/London', 'Europe/Copenhagen')
    -> ;
+-------------------------------------------------------------------------+
| CONVERT_TZ('2012-01-01 00:00:00', 'Europe/London', 'Europe/Copenhagen') |
+-------------------------------------------------------------------------+
| 2012-01-01 01:00:00                                                     |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)