MySQL Bugs: #12445: convert_tz returns null if named time zone used

Bug #12445	convert_tz returns null if named time zone used
Submitted:	8 Aug 2005 20:39	Modified:	21 Jul 2010 5:47
Reporter:	Jacek Becla	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.0.9-beta-standard, 5.1	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any

Description:
convert_tz returns null if named time zone used.

SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
+-----------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','GMT','MET') |
+-----------------------------------------------+
| NULL                                          |
+-----------------------------------------------+
1 row in set (0.00 sec)

 
Offset from UTC works fine:

SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+01:00');
+-----------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','+00:00','+01:00') |
+-----------------------------------------------------+
| 2004-01-01 13:00:00                                 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

How to repeat:
SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');

Jacek, could you pleae check if timezone tables is filled in the mysql database?

use mysql;
select count(*) from time_zone;

the point is:
  You need to fill some tables before using named timezones. More detailed description you can find here: http://dev.mysql.com/doc/mysql/en/time-zone-support.html

Aleksey, you're correct, the timezone table was not loaded. It would probably be useful to document it better, or issue a warning if this table is not loaded and someone is trying to use named time zones... just a suggestion.

OK, I re-qualify this bug report as a 'feature request' - if timezone tables are empty, mysql must raise warning in case of using convert_tz function (and other functions that depend on timezone)

See also bug #64599