TZ doesn't work for 2 same conversions

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.

---

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.

链接地址: http://www.djcxy.com/p/25052.html

上一篇: 处理PHP和MySQL中的时区

下一篇: TZ不适用于2次相同的转换