CONVERT_TZ () TIDB result differ with mysql in few cases:

[ramanich1]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

SELECT
CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Central') AS time1,
CONVERT_TZ('2007-03-11 3:00:00','US/Eastern','US/Central') AS time2;

2. What did you expect to see? (Required)

+---------------------+---------------------+
| time1               | time2               |
+---------------------+---------------------+
| 2007-03-11 01:00:00 | 2007-03-11 01:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

+-------+---------------------+
| time1 | time2               |
+-------+---------------------+
| NULL  | 2007-03-11 01:00:00 |
+-------+---------------------+
1 row in set (0.00 sec)

4. What is your TiDB version? (Required)

| Release Version: v5.4.0-alpha-67-g17edc5758
Edition: Community
Git Commit Hash: 17edc5758fbf865cada7e156318c41d3ee8a7501
Git Branch: master
UTC Build Time: 2021-11-16 02:57:45
GoVersion: go1.17.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

[sylzd]

/assign

[sylzd]

cannot reproduce at 17edc5758fbf865cada7e156318c41d3ee8a7501 and master branch, any special config set?

[ramanich1]

Nothing specific :
Time_zone = system;
sql_mode ='';

[morgo]

@sylzd I can reproduce with default SQL mode, any timezone (or at least my time + UTC) using the hash you provided:

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

tidb> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v5.4.0-alpha-67-g17edc5758
Edition: Community
Git Commit Hash: 17edc5758fbf865cada7e156318c41d3ee8a7501
Git Branch: HEAD
UTC Build Time: 2021-11-24 16:28:23
GoVersion: go1.16.9
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

tidb> SET time_zone='UTC';
Query OK, 0 rows affected (0.00 sec)

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

tidb> SET sql_mode = DEFAULT;
Query OK, 0 rows affected (0.00 sec)

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

It is only time1 which is invalid. This might be related to Daylight savings - when comparing to MySQL make sure to load the timezone database (it is loaded in TiDB by default, but not MySQL).

[kennytm]

2007-03-11 02:00:00 is a non-existing local time in all US time zones observing DST, as it is the beginning of that year's spring-forward. I believe this is a duplicate of #28739.

[morgo]

2007-03-11 02:00:00 is a non-existing local time in all US time zones observing DST, as it is the beginning of that year's spring-forward. I believe this is a duplicate of #28739.

Thanks! I will comment on this issue.

[sylzd]

that is weird. my result is like that. It seems related to the DST server configure. I'll keep concerned about #28739.

tidb > SELECT
    -> CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Central') AS time1,
    -> CONVERT_TZ('2007-03-11 3:00:00','US/Eastern','US/Central') AS time2;
+---------------------+---------------------+
| time1               | time2               |
+---------------------+---------------------+
| 2007-03-10 12:00:00 | 2007-03-10 13:00:00 |
+---------------------+---------------------+
1 row in set (0.01 sec)

Thu Nov 25 11:28:29 2021
tidb>
Thu Nov 25 14:46:32 2021
tidb> SELECT tidb_version()\G;
*************************** 1. row ***************************
tidb_version(): Release Version: v5.3.0-alpha-1380-g17edc5758
Edition: Community
Git Commit Hash: 17edc5758fbf865cada7e156318c41d3ee8a7501
Git Branch: HEAD
UTC Build Time: 2021-11-24 10:50:55
GoVersion: go1.16.3
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.01 sec)

ERROR:
No query specified

Thu Nov 25 14:49:43 2021
tidb > SET time_zone='UTC';
Query OK, 0 rows affected (0.01 sec)

Thu Nov 25 14:50:07 2021
tidb > SELECT CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Centra
l') AS time1, CONVERT_TZ('2007-03-11 3:00:00','US/Eastern','US/Central') AS time2;
+---------------------+---------------------+
| time1               | time2               |
+---------------------+---------------------+
| 2007-03-10 12:00:00 | 2007-03-10 13:00:00 |
+---------------------+---------------------+
1 row in set (0.01 sec)

Thu Nov 25 14:50:12 2021
tidb > SET sql_mode = DEFAULT;
Query OK, 0 rows affected (0.01 sec)

Thu Nov 25 14:50:20 2021
tidb > SELECT CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Centra
l') AS time1, CONVERT_TZ('2007-03-11 3:00:00','US/Eastern','US/Central') AS time2;
+---------------------+---------------------+
| time1               | time2               |
+---------------------+---------------------+
| 2007-03-10 12:00:00 | 2007-03-10 13:00:00 |
+---------------------+---------------------+
1 row in set (0.01 sec)
tidb > SELECT CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Centra
l') AS time1, CONVERT_TZ('2007-03-11 3:00:00','US/Eastern','US/Central') AS time2;
+---------------------+---------------------+
| time1               | time2               |
+---------------------+---------------------+
| 2007-03-10 12:00:00 | 2007-03-10 13:00:00 |
+---------------------+---------------------+
1 row in set (0.01 sec)

[jackysp]

Tested in 2970dd4 , the bug is still here. I think it also affects release-6.2. And I got some report it happened in release-4.0.

TiDB:

mysql> select CONVERT_TZ('2022-09-04 00:00:00', 'America/Santiago', 'Asia/Shanghai');
+------------------------------------------------------------------------+
| CONVERT_TZ('2022-09-04 00:00:00', 'America/Santiago', 'Asia/Shanghai') |
+------------------------------------------------------------------------+
| NULL                                                                   |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select CONVERT_TZ('2022-09-04 01:00:00', 'America/Santiago', 'Asia/Shanghai');
+------------------------------------------------------------------------+
| CONVERT_TZ('2022-09-04 01:00:00', 'America/Santiago', 'Asia/Shanghai') |
+------------------------------------------------------------------------+
| 2022-09-04 12:00:00                                                    |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL:

mysql> select CONVERT_TZ('2022-09-04 00:00:00', 'America/Santiago', 'Asia/Shanghai');
+------------------------------------------------------------------------+
| CONVERT_TZ('2022-09-04 00:00:00', 'America/Santiago', 'Asia/Shanghai') |
+------------------------------------------------------------------------+
| 2022-09-04 12:00:00                                                    |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select CONVERT_TZ('2022-09-04 01:00:00', 'America/Santiago', 'Asia/Shanghai');
+------------------------------------------------------------------------+
| CONVERT_TZ('2022-09-04 01:00:00', 'America/Santiago', 'Asia/Shanghai') |
+------------------------------------------------------------------------+
| 2022-09-04 12:00:00                                                    |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

And I also test it in Postgresql, it seems to have the same behavior as MySQL.

SELECT ((timestamp '2022-09-04 00:00:00') AT TIME ZONE 'America/Santiago') AT TIME ZONE 'Asia/Shanghai';

2022-09-04T12:00:00.000Z

SELECT ((timestamp '2022-09-04 01:00:00') AT TIME ZONE 'America/Santiago') AT TIME ZONE 'Asia/Shanghai';

2022-09-04T12:00:00.000Z