MySQL Bugs: #14062: Select convert_tz(now(),'EST','IST') returns null

Bug #14062	Select convert_tz(now(),'EST','IST') returns null
Submitted:	16 Oct 2005 22:59	Modified:	17 Oct 2005 9:38
Reporter:	Mahaveer Jain	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S2 (Serious)
Version:	4.1.14	OS:	Windows (Window XP)
Assigned to:		CPU Architecture:	Any

Description:
I have runned mysql_fix_privilege_tables.sql SQL script that you can run using the mysql client. 

D:\> D:\Program Files\MySQL\MySQL Server 4.1.10\bin\mysql -u root -p mysql 
mysql> SOURCE D:/Program Files/MySQL/MySQL Server 4.1.10/scripts/mysql_fix_privilege_tables.sql 

Then I have installed the data table for timezone table from the link below 

http://www.trans-alliance.am/Downloads/Contrib/timezone-2004e.zip. 

I am trying to run the query 

Select convert_tz(now(),'EST','IST') 

it returns null 

I have tried with other timezone too and nothing helps. 

The explaination for this query is given in the link below. 
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html 

Also it doesn't allow me to set the user timezone. It allows me to set the user timezone to only MET, GMT or EST

Mahaveer

How to repeat:
Run this query 

Select convert_tz(now(),'EST','IST') 

Select convert_tz(now(),'EST','CST')

Thank you for a problem report. I've tried to repeat the problem you described on latest 4.1.16 build on Linux, after carefully performing the steps described in the manual (http://dev.mysql.com/doc/refman/4.1/en/time-zone-support.html).

The result is the following: if you specify really existing time zone, by name, function works. If there is no such zone in the time_zone_tables, CONVERT_TZ returns you NULL (just as described in http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html):

mysql> Select convert_tz(now(),'EST','GMT');
+-------------------------------+
| convert_tz(now(),'EST','GMT') |
+-------------------------------+
| 2005-10-17 17:24:28           |
+-------------------------------+
1 row in set (0,00 sec)

mysql> Select convert_tz(now(),'GMT','CST');
+-------------------------------+
| convert_tz(now(),'GMT','CST') |
+-------------------------------+
| NULL                          |
+-------------------------------+
1 row in set (0,00 sec)

mysql> Select convert_tz(now(),'GMT','CDT');
+-------------------------------+
| convert_tz(now(),'GMT','CDT') |
+-------------------------------+
| NULL                          |
+-------------------------------+
1 row in set (0,00 sec)

mysql> select * from time_zone_name where name like '%CDT%' or name like '%CST%'
;
+-----------------------+--------------+
| Name                  | Time_zone_id |
+-----------------------+--------------+
| CST6CDT               |          313 |
| SystemV/CST6          |          504 |
| SystemV/CST6CDT       |          505 |
| posix/CST6CDT         |          846 |
| posix/SystemV/CST6    |         1037 |
| posix/SystemV/CST6CDT |         1038 |
| right/CST6CDT         |         1380 |
| right/SystemV/CST6    |         1571 |
| right/SystemV/CST6CDT |         1572 |
+-----------------------+--------------+
9 rows in set (0,04 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.16-debug |
+--------------+
1 row in set (0,00 sec)

So, please, check your tables for the timezone names you are using. 

Also, you may try to download and install the appropriate tables from http://dev.mysql.com/downloads/timezones.html (although, they may be the same as yours). 

Looks like it is simply a problem of setting the right name for the timezone you need.

The timezone tables are only created but not populated by the 
install and upgrade scripts, you have to populate these yourself
from your systems timezone data, see

http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html

The link doesn't work 

http://dev.mysql.com/downloads/timezones.html 

I am not sure the table I have are fine.
mysql>select * from time_zone_name where name like '%CDT%' or name like '%CST%'; 

I get this result.

Name                Time_zone_id
CST6CDT               324
SystemV/CST6        516
SystemV/CST6CDT   517

Also I want to know if I can use short hand form in convert_tz(), i.e, GMT, CST, CDT.

I am using window and I cannot update the tables using my system table. So you check the link in the above URL.