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
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.6, 5.7
• OS: Linux
• 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] MySQL Verification Team

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)