CODEWITHSUNDEEP
mysqltimezoneconvert-tz
Lu Shi
Lu Shi

Reputation: 93

MySQL CONVERT_TZ() returns null when one of the arguments is "+14:00"

I am trying to convert UTC time into the users local time, it works fine until I try to convert the time to +14:00 timezone, the result is always null, anyone has idea? Here is my code:

select CONVERT_TZ(now(), '+00:00', '+14:00')

Upvotes: 8

Views: 5125

Answers (2)

instead
instead

Reputation: 3471

You can fix that, by using timezone name instead of offset. UTC +14:00 equals to Pacific/Kiritimati timezone. So if You modify query to:

SELECT CONVERT_TZ(NOW(), '+00:00', 'Pacific/Kiritimati')

or

SELECT CONVERT_TZ(NOW(), 'UTC', 'Pacific/Kiritimati')

then You'll get valid date, not NULL.

BUT there is one condition. Your MySQL engine needs timezones list. If above queries still returns NULL, it means You don't have timezones list loaded into Your MySQL engine.

Probably You'll have to run also this command:

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

Plese check this thread for more information, about loading timezones list: convert_tz returns null

Tested on MySQL 5.6.36.

Upvotes: 1

Barmar
Barmar

Reputation: 781370

This is a known bug in MySQL:

MySQL does not recognize timezone offset UTC +14:00

Upvotes: 5

Related Questions