Bug #90043 convert_tz doesn't handle offset more than 13 hours
Submitted: 13 Mar 2018 8:53 Modified: 14 Mar 2018 17:27
Reporter: Vadim s Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6, 5.7 OS:Linux
Assigned to: CPU Architecture:Any
Tags: convert_tz, timezone offset

[13 Mar 2018 8:53] Vadim s
Description:
We encoutered an issue using this function with offset that is more than +13 hours. Currently there is timezone "Pacific/Chatham" that has +13:45 as DST offset and it is now in use.
Once specifying to convert timestamp to this timezone as name it works as expected, but if offset is specified as "+13:45" it returns NULL.

How to repeat:
SELECT CONVERT_TZ('2018-03-01 12:00:00','+00:00','Pacific/Chatham');
-- returns 2018-03-02 01:45:00

SELECT CONVERT_TZ('2018-03-01 12:00:00','+00:00','+13:45');
-- returns NULL

Suggested fix:
Need to allow offset specification up to +14:00
[14 Mar 2018 17:27] Bogdan Kecman
Hi Vadim,

Thanks for the report. Verified as reported, everything over 13h returns null
Bogdan

mysql [localhost] {msandbox} ((none)) > SELECT CONVERT_TZ('2018-03-01 12:00:00','+00:00','Pacific/Chatham');
+--------------------------------------------------------------+
| CONVERT_TZ('2018-03-01 12:00:00','+00:00','Pacific/Chatham') |
+--------------------------------------------------------------+
| NULL                                                         |
+--------------------------------------------------------------+
1 row in set (0.02 sec)

mysql [localhost] {msandbox} ((none)) > SELECT CONVERT_TZ('2018-03-01 12:00:00','+00:00','+13:45');
+-----------------------------------------------------+
| CONVERT_TZ('2018-03-01 12:00:00','+00:00','+13:45') |
+-----------------------------------------------------+
| NULL                                                |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} ((none)) > SELECT CONVERT_TZ('2018-03-01 12:00:00','+00:00','+12:45');
+-----------------------------------------------------+
| CONVERT_TZ('2018-03-01 12:00:00','+00:00','+12:45') |
+-----------------------------------------------------+
| 2018-03-02 00:45:00                                 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} ((none)) > SELECT CONVERT_TZ('2018-03-01 12:00:00','+00:00','+13:45');
+-----------------------------------------------------+
| CONVERT_TZ('2018-03-01 12:00:00','+00:00','+13:45') |
+-----------------------------------------------------+
| NULL                                                |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} ((none)) > SELECT CONVERT_TZ('2018-03-01 12:00:00','+00:00','+13:30');
+-----------------------------------------------------+
| CONVERT_TZ('2018-03-01 12:00:00','+00:00','+13:30') |
+-----------------------------------------------------+
| NULL                                                |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} ((none)) > SELECT CONVERT_TZ('2018-03-01 12:00:00','+00:00','+13:10');
+-----------------------------------------------------+
| CONVERT_TZ('2018-03-01 12:00:00','+00:00','+13:10') |
+-----------------------------------------------------+
| NULL                                                |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} ((none)) > SELECT CONVERT_TZ('2018-03-01 12:00:00','+00:00','+13:00');
+-----------------------------------------------------+
| CONVERT_TZ('2018-03-01 12:00:00','+00:00','+13:00') |
+-----------------------------------------------------+
| 2018-03-02 01:00:00                                 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} ((none)) > SELECT CONVERT_TZ('2018-03-01 12:00:00','+00:00','+13:01');
+-----------------------------------------------------+
| CONVERT_TZ('2018-03-01 12:00:00','+00:00','+13:01') |
+-----------------------------------------------------+
| NULL                                                |
+-----------------------------------------------------+
1 row in set (0.00 sec)