MySQL Bugs: #4434: New time zone functions missing data and documentation

Bug #4434	New time zone functions missing data and documentation
Submitted:	7 Jul 2004 15:15	Modified:	30 Jul 2004 16:15
Reporter:	Rick Robinson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.3	OS:	Any (All)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
The new time zone capability added to 4.1.3 is missing several key pieces:
- The time_zone_* tables in the mysql database are all empty.  These require data in order for the new CONVERT_TZ function to work correctly.
- There is no documentation for the new CONVERT_TZ function in the date functions section.
- There is no documentation on the new system variable 'system_time_zone'.
- The documentation for new system variable 'time_zone' incorrectly lists it as 'timezone'.

How to repeat:
On a 4.1.3 binary install, try:
select convert_tz(current_timestamp(), 'US/Pacific', 'US/Eastern');

Will receive NULL.  Should receive a valid value.

In the mysql database, try:
select * from time_zone;

Will receive 'Empty set'.  Should receive a whole mess of data.

Suggested fix:
Add all the required mysql.time_zone_* table data to the installation script (analgous to the help_* table data load).
Add/fix the documentation to include a full description of the CONVERT_TZ function and the new system variables.

Hi, Rick!

Thank you for your interest in MySQL and for testing our new features!

Your bug report consists from two parts:

1st part is missing documentation about time zone functionality and about related variable naming chages. Documentation proposal was already sent to MySQL documentation team thus manual should be updated accordingly really soon.

2nd part is request for bundling of time zone data with MySQL and automatical filling of system time zone describing tables. Again work on this issue already started :)

Meanwhile if you are working on some Unix-like OS you can use mysql_tzinfo_to_sql utility which is bundled with MySQL for filling mysql.time_zone* tables. 
For this you need to run mysql_tzinfo_to_sql with path to directory containing zoneinfo files (usually it is something like /usr/share/zoneinfo/) as argument and pipe generated SQL to input of mysql commad-line client connected to 'mysql' database as root user.

So something like the following should do the trick:
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

manual is updated