MySQL CONVERT_TZ doesn't work for 2 same conversions

Question

I got a table with event time logs, and I need to convert 2 columns to a common timezone.

So if I do (convert to different timezones)

        SELECT id
            ,CONVERT_TZ(starting_time, 'UTC', 'Europe/London') AS starting_time
            ,CONVERT_TZ(finishing_time, 'UTC', 'Europe/Madrid') AS finishing_time
        FROM table

I got all the times, but if I run

        SELECT id
            ,CONVERT_TZ(starting_time, 'UTC', 'Europe/London') AS starting_time
            ,CONVERT_TZ(finishing_time, 'UTC', 'Europe/London') AS finishing_time
        FROM table

All the time data gets NULL.

I loaded my TZ data with the information here https://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html from /usr/share/zoneinfo on RHEL 5 using MySQL 5.1.63.

Answer 1

This will happen if you haven't loaded the time zone table into MySQL. Try this (in console of RHEL):

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

EDIT: You only need to load table once. It loads the information into a table into MySQL, which is used from then on.

EDIT2: Named time zones can be used only if the time zone information tables in the MySQL database have been created and populated.

The MySQL installation procedure creates the time zone tables in the MySQL database, but does not load them.

See the version 5.1.x documentation for details.