insert into timestamp column with daylight saving affected time generate different results from MySQL
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
Please answer these questions before submitting your issue. Thanks!
### 1. Minimal reproduce step (Required)
> Note timestamp value is inserted under ` Asia/Shanghai` timezone
```
mysql> select @@time_zone;
+---------------+
| @@time_zone |
+---------------+
| Asia/Shanghai |
+---------------+
1 row in set (0.00 sec)
mysql> desc timestamp_table;
+-------+-----------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+------+---------+-------+
| id | int(11) | YES | | NULL | |
| value | timestamp | YES | | NULL | |
+-------+-----------+------+------+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into timestamp_table values(1,'1990-09-16 01:59:59'),(1,'1990-09-16 02:00:00');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into timestamp_table values(1,'1990-09-16 00:59:59'),(1,'1990-09-16 01:00:00');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> set time_zone='UTC';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from timestamp_table;
+------+---------------------+
| id | value |
+------+---------------------+
| 1 | 1990-09-15 17:59:59 |
| 1 | 1990-09-15 18:00:00 |
| 1 | 1990-09-15 15:59:59 |
| 1 | 1990-09-15 17:00:00 |
+------+---------------------+
4 rows in set (0.01 sec)
```
### 2. What did you expect to see? (Required)
```
mysql> set time_zone='UTC';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from timestamp_table;
+------+---------------------+
| id | value |
+------+---------------------+
| 1 | 1990-09-15 16:59:59 |
| 1 | 1990-09-15 18:00:00 |
| 1 | 1990-09-15 15:59:59 |
| 1 | 1990-09-15 16:00:00 |
+------+---------------------+
4 rows in set (0.01 sec)
```
### 3. What did you see instead (Required)
```
mysql> set time_zone='UTC';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from timestamp_table;
+------+---------------------+
| id | value |
+------+---------------------+
| 1 | 1990-09-15 17:59:59 |
| 1 | 1990-09-15 18:00:00 |
| 1 | 1990-09-15 15:59:59 |
| 1 | 1990-09-15 17:00:00 |
+------+---------------------+
4 rows in set (0.01 sec)
```
### 4. What is your TiDB version? (Required)
```
mysql> select tidb_version();
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v5.5.0-alpha-202-g077eb80f1
Edition: Community
Git Commit Hash: 077eb80f1317a54b1a73894d9f58cd6dfff19260
Git Branch: master
UTC Build Time: 2022-02-08 04:27:22
GoVersion: go1.17.1
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```
Contributor guide
Assessment
This issue has not been assessed yet.