pingcap / pingcap/tidb

insert into timestamp column with daylight saving affected time generate different results from MySQL

Open
#32,226 2 comments 0 reactions 1 assignee Claimed by @mjonss View on GitHub
severity/moderate sig/execution type/bug
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.