CONVERT_TZ invalid time format

[breezewish]

Bug Report

MySQL:

mysql> SELECT CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Central');
+------------------------------------------------------------+
| CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Central') |
+------------------------------------------------------------+
| 2007-03-11 01:00:00                                        |
+------------------------------------------------------------+
1 row in set (0.00 sec)

TiDB:

mysql> SELECT CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Central');
ERROR 1292 (22007): invalid time format: '{2007 3 11 2 0 0 0}'

[tiancaiamao]

Thanks for your report. @breeswish

[ghost]

This now returns NULL in TiDB. It will be NULL in MySQL too by default, since the TZ tables need to be loaded separately - but since TiDB doesn't have this separate step, it can be considered an incorrect result:

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-852-g1e7454c81
Edition: Community
Git Commit Hash: 1e7454c81d1f4fae464e835d1a4023a911cfa87b
Git Branch: master
UTC Build Time: 2020-07-28 01:36:23
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

mysql> SELECT CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Central');
+------------------------------------------------------------+
| CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Central') |
+------------------------------------------------------------+
| NULL                                                       |
+------------------------------------------------------------+
1 row in set (0.00 sec)

[breezewish]

@nullnotnil Yes, TiDB embeds time zone information by default (as other time zone related functions will work), a proper behaviour should be returning something instead of NULL.

[dveeden]

SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET'); -- works correctly (example from https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_convert-tz)
SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00'); -- works correctly (example from https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_convert-tz

SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','+10:00'); -- returns null, a bug
SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','MET'); -- returns null, a bug

SELECT CONVERT_TZ('2004-01-01 12:00:00','SYSTEM','+10:00'); -- returns null, a bug, behavior different from mysql
SELECT CONVERT_TZ('2004-01-01 12:00:00','SYSTEM','MET'); -- returns null, a bug, behavior different from mysql

[dveeden]

The problem is here: https://github.com/pingcap/tidb/blob/master/expression/builtin_time.go#L5721-L5755

If both are timezone offsets or if both are timezone names things are fine, but when mixed it doesn't work which matches what the if statements do based on

 MySQL  127.0.0.1:4000 ssl  SQL > SELECT CONVERT_TZ('2004-01-01 12:00:00','UTC','+00:00');
+--------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','UTC','+00:00') |
+--------------------------------------------------+
| NULL                                             |
+--------------------------------------------------+
1 row in set (0.0009 sec)
 MySQL  127.0.0.1:4000 ssl  SQL > SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','UTC');
+--------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','+00:00','UTC') |
+--------------------------------------------------+
| NULL                                             |
+--------------------------------------------------+
1 row in set (0.0006 sec)
 MySQL  127.0.0.1:4000 ssl  SQL > SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+00:00');
+-----------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','+00:00','+00:00') |
+-----------------------------------------------------+
| 2004-01-01 12:00:00                                 |
+-----------------------------------------------------+
1 row in set (0.0007 sec)
 MySQL  127.0.0.1:4000 ssl  SQL > SELECT CONVERT_TZ('2004-01-01 12:00:00','UTC','UTC');
+-----------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','UTC','UTC') |
+-----------------------------------------------+
| 2004-01-01 12:00:00                           |
+-----------------------------------------------+
1 row in set (0.0013 sec)

Looking at the regex it doesn't seem to handle +14:00 correctly.

[bolt-juri-gavshin]

In addition to '+14:00', please don't forget about the 'SYSTEM' timezone, which is supported in Mysql and is a default value of @@session.time_zone/@@global.time_zone variables.

[bolt-juri-gavshin]

@dveeden I am the one who sent the original commands with comments, that reached you through multiple people :).
I think #11932 and this one are very much related to each other (one is about bad formatting another is about impossible dates), but my example with offset->named and named->offset conversion is a very separate issue - in my examples datetimes are correct, both format-wise and date-wise...
And the 'SYSTEM' is just another "correct" value, that is not being processed in the code you mentioned.

[dveeden]

Current behavior of the query in the description of this issue:

 MySQL  127.0.0.1:4000 ssl  test  SQL > SELECT CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Central');
+------------------------------------------------------------+
| CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Central') |
+------------------------------------------------------------+
| NULL                                                       |
+------------------------------------------------------------+
1 row in set (0.0009 sec)
 MySQL  127.0.0.1:4000 ssl  test  SQL > SELECT CONVERT_TZ('2007-04-11 2:00:00','US/Eastern','US/Central');
+------------------------------------------------------------+
| CONVERT_TZ('2007-04-11 2:00:00','US/Eastern','US/Central') |
+------------------------------------------------------------+
| 2007-04-11 01:00:00                                        |
+------------------------------------------------------------+
1 row in set (0.0016 sec)
 MySQL  127.0.0.1:4000 ssl  test  SQL > SELECT CONVERT_TZ('2007-03-11 2:00:00','EST','US/Central');
+-----------------------------------------------------+
| CONVERT_TZ('2007-03-11 2:00:00','EST','US/Central') |
+-----------------------------------------------------+
| 2007-03-11 01:00:00                                 |
+-----------------------------------------------------+
1 row in set (0.0014 sec)
 MySQL  127.0.0.1:4000 ssl  test  SQL > SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v5.3.0-alpha-1209-g19a2b3c31
Edition: Community
Git Commit Hash: 19a2b3c31106470f83696f8fdaf443f150061a66
Git Branch: master
UTC Build Time: 2021-10-26 12:13:58
GoVersion: go1.16.8
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.0003 sec)