Mysql and timezone data

[lordlinus]

hi, i have imported timezone information on QNAP using the command

Code: Select all

mysql_tzinfo_to_sql tz_file tz_name | mysql -u root mysql

and i can see the info is present in the mysql table. but when i

Code: Select all

select CONVERT_TZ(mydate,'GMT','Australia/Melbourne') from mytable;

i get all NULL results. mytable contains date in the format "2012-01-01 13:59:59". Any help on how i can use CONVERT_TZ to convert from one timezone to other time zones?
Thanks

[lordlinus]

Anyone? i cant be the only person having trouble. (or am i? )

[schumaku]

If GMT a timezone - or say is it a timezone that exist in the table exported from the system by mysql_tzinfo_to_sql?

Code: Select all

mysql> SELECT CONVERT_TZ('2012-01-01 12:00:00','GMT','Australia/Melbourne');   
+---------------------------------------------------------------+
| CONVERT_TZ('2012-01-01 12:00:00','GMT','Australia/Melbourne') |
+---------------------------------------------------------------+
| NULL                                                          |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

http://dev.mysql.com/doc/refman/5.1/en/ ... convert-tz says:

CONVERT_TZ() converts a datetime value dt from the time zone given by from_tz to the time zone given by to_tz and returns the resulting value. Time zones are specified as described in Section 10.6, “MySQL Server Time Zone Support”. This function returns NULL if the arguments are invalid.

Code: Select all

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

Now, why does ie. GMT not exist?

[~] # /mnt/ext/opt/mysql/bin/mysql_tzinfo_to_sql /etc/zoneinfo | /mnt/ext/opt/mysql/bin/mysql -u root mysql -p
Enter password:
Warning: Unable to load '/etc/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/etc/zoneinfo/zone.tab' as time zone. Skipping it.

Well, the two skipped tab files are not related. Even more, we need to understand the constraints of the embedded Linux system. Most likley, the tables available in /etc/zoneinfo are simply a subset of the (hughe!) list on a generic U**x or Linux system.

Instead of importing the subset from the NAS zone info, you can consider using the pre-made time zone description tables instead.

Be aware, your application should only make use of time conversions within MySQL, not mixing sytem and MySQL time conversion tables then to avoid any possible inconsitencies.

Regards,
-Kurt.

PS. Send beer ...