Bug #51852 inconsistency in timezone table
Submitted: 9 Mar 2010 5:41 Modified: 9 Apr 2010 9:03
Reporter: Alagarsamy A Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1.44 OS:Linux
Assigned to: CPU Architecture:Any

[9 Mar 2010 5:41] Alagarsamy A 
Description:
i used mysql_tzinfo_to_sql utility to create timezone_* database  after upgrading zoneinfo
in my host.

after that i am seeing this mismatch. 

'America/Sao_Paulo' (http://www.timeanddate.com/worldclock/city.html?n=233) and
'America/Buenos_Aires' (http://www.timeanddate.com/worldclock/city.html?n=51) are
supposed to have same time information .. but mysql database shows differently ..  

is this a bug somewhere ? 

mysql> select * from time_zone_name where Time_zone_id in (59,185);
+--------------------------------+--------------+
| Name                           | Time_zone_id |
+--------------------------------+--------------+
| America/Argentina/Buenos_Aires |           59 |
| America/Sao_Paulo              |          185 |
+--------------------------------+--------------+
2 rows in set (0.00 sec)

mysql> select * from time_zone_transition_type where Time_zone_id=185;
+--------------+--------------------+--------+--------+--------------+
| Time_zone_id | Transition_type_id | Offset | Is_DST | Abbreviation |
+--------------+--------------------+--------+--------+--------------+
|          185 |                  0 | -11188 |      0 | LMT          |
|          185 |                  1 |  -7200 |      1 | BRST         |
|          185 |                  2 | -10800 |      0 | BRT          |
+--------------+--------------------+--------+--------+--------------+
3 rows in set (0.00 sec)

mysql> select * from time_zone_transition_type where Time_zone_id=59;
+--------------+--------------------+--------+--------+--------------+
| Time_zone_id | Transition_type_id | Offset | Is_DST | Abbreviation |
+--------------+--------------------+--------+--------+--------------+
|           59 |                  0 | -15408 |      0 | CMT          |
|           59 |                  1 | -14400 |      0 | ART          |
|           59 |                  2 | -10800 |      1 | ARST         |
|           59 |                  3 |  -7200 |      1 | ARST         |
|           59 |                  4 | -10800 |      0 | ART          |
+--------------+--------------------+--------+--------+--------------+
5 rows in set (0.00 sec)

mysql> select convert_tz(now(), @@global.time_zone, 'America/Sao_Paulo');
+------------------------------------------------------------+
| convert_tz(now(), @@global.time_zone, 'America/Sao_Paulo') |
+------------------------------------------------------------+
| 2010-03-08 17:48:16                                        |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz(now(), @@global.time_zone,'America/Buenos_Aires');
+--------------------------------------------------------------+
| convert_tz(now(), @@global.time_zone,'America/Buenos_Aires') |
+--------------------------------------------------------------+
| 2010-03-08 18:48:28                                          |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

this is with mysql-5.1.44

A.Alagarsamy

How to repeat:
mysql> select convert_tz(now(), @@global.time_zone, 'America/Sao_Paulo');
+------------------------------------------------------------+
| convert_tz(now(), @@global.time_zone, 'America/Sao_Paulo') |
+------------------------------------------------------------+
| 2010-03-08 17:48:16                                        |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz(now(), @@global.time_zone,'America/Buenos_Aires');
+--------------------------------------------------------------+
| convert_tz(now(), @@global.time_zone,'America/Buenos_Aires') |
+--------------------------------------------------------------+
| 2010-03-08 18:48:28                                          |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
[9 Mar 2010 9:03] Sveta Smirnova 
Thank you for the report.

mysql_tzinfo_to_sql just converts timezone files from your OS, so it can be inserted in SQL tables and I can not repeat differencies in my environment:

$mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql51 mysql
Warning: Unable to load '/usr/share/zoneinfo/+VERSION' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.

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

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

mysql>  select convert_tz(now(), @@global.time_zone, 'America/Sao_Paulo');
+------------------------------------------------------------+
| convert_tz(now(), @@global.time_zone, 'America/Sao_Paulo') |
+------------------------------------------------------------+
| 2010-03-09 06:01:08                                        |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>  select convert_tz(now(), @@global.time_zone,'America/Buenos_Aires');
+--------------------------------------------------------------+
| convert_tz(now(), @@global.time_zone,'America/Buenos_Aires') |
+--------------------------------------------------------------+
| 2010-03-09 06:01:11                                          |
+--------------------------------------------------------------+
1 row in set (0.01 sec)

Please check if timezone settings in your OS are correct.
[9 Apr 2010 23:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".