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

Question

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')

@Barmar: Such is the way of politics: it doesn't *have* to make sense. While there might be bureaucratic reasons for it, the boast "we are ahead of the world" is probably most of the justification. — wallyk, Oct 01 '15 at 17:01
@Mr.Llama That will work if you only care about the time of day, but the date will be a day behind. — Barmar, Oct 01 '15 at 18:30

score 5 · Accepted Answer · answered Oct 01 '15 at 16:55

5

This is a known bug in MySQL:

MySQL does not recognize timezone offset UTC +14:00

answered Oct 01 '15 at 16:55

Barmar

741,623
53
500
612

Thank you! I notice that the submitted date of this bug is in 2011, is there anyway to fix it now? – Lu Shi Oct 02 '15 at 15:04
Probably not. If they'd actually done anything about it, there would be an update – Barmar Oct 02 '15 at 15:37

instead · Answer 2 · 2019-10-16T19:17:40.717

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.

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

2 Answers2