现象
插入时间报错：
- mysql 中
ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 00:01:00' for column 'agent_start_time' at row 1
- TIDB 中
ERROR 1292 (22007): invalid time format: '{1970 1 1 0 1 0 0}'
原因
TIMESTAMP 包含了日期和时间部分，值的范围是UTC时间 '1970-01-01 00:00:01' 到 '2038-01-19 03:14:07'
- 当插入时间
1970-01-01 00:01:00报错的原因：
1.sql_mode 包涵了STRICT_TRANS_TABLES：严格模式，非法数据值被拒绝
2.系统时区非 UTC ，time_zone 为 system或者为 '+8:00'
解决方案
- 1.修改 sql_mode ,删除 STRICT_TRANS_TABLES 模式，但是会改变数据为默认
- 2.修改 linux 系统时区或者设置 time_zone 为'+0:00'，可以保留原数据
测试
- MySQL 中: 修改 sql_mode || time_zone
(root@localhost) [test1]>select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
(root@localhost) [test1]>CREATE TABLE `b` (
-> `id` int(11) DEFAULT NULL,
-> `agent_start_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [test1]>show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)
(root@localhost) [test1]>insert into b values(2,"1970-01-01 00:01:00");
ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 00:01:00' for column 'agent_start_time' at row 1
(root@localhost) [test1]>set sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test1]>insert into b values(2,"1970-01-01 00:01:00");
Query OK, 1 row affected, 1 warning (0.00 sec)
(root@localhost) [test1]>select * from b;
+------+---------------------+
| id | agent_start_time |
+------+---------------------+
| 2 | 0000-00-00 00:00:00 |
+------+---------------------+
1 row in set (0.00 sec)
(root@localhost) [test1]>set sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)
(root@localhost) [test1]>set time_zone='+0:00';
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test1]>insert into b values(2,"1970-01-01 00:01:00");
Query OK, 1 row affected (0.00 sec)
(root@localhost) [test1]>select * from b;
+------+---------------------+
| id | agent_start_time |
+------+---------------------+
| 2 | 0000-00-00 00:00:00 |
| 2 | 1970-01-01 00:01:00 |
+------+---------------------+
2 rows in set (0.00 sec)
- TiDB: 修改 sql_mode || time_zone
(root@10.0.1.8) [test1]>select @@sql_mode;
+--------------------------------------------+
| @@sql_mode |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)
(root@10.0.1.8) [test1]>CREATE TABLE `b` (
-> `id` int(11) DEFAULT NULL,
-> `agent_start_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.15 sec)
(root@10.0.1.8) [test1]>insert into b values(2,"1970-01-01 00:01:00");
ERROR 1292 (22007): invalid time format: '{1970 1 1 0 1 0 0}'
(root@10.0.1.8) [test1]>set sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
(root@10.0.1.8) [test1]>insert into b values(2,"1970-01-01 00:01:00");
Query OK, 1 row affected, 1 warning (0.14 sec)
(root@10.0.1.8) [test1]>select * from b;
+------+---------------------+
| id | agent_start_time |
+------+---------------------+
| 2 | 0000-00-00 00:00:00 |
+------+---------------------+
1 row in set (0.01 sec)
(root@10.0.1.8) [test1]>set sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
(root@10.0.1.8) [test1]>set time_zone='+0:00';
Query OK, 0 rows affected (0.00 sec)
(root@10.0.1.8) [test1]>insert into b values(2,"1970-01-01 00:01:00");
Query OK, 1 row affected (0.01 sec)
(root@10.0.1.8) [test1]>select * from b;
+------+---------------------+
| id | agent_start_time |
+------+---------------------+
| 2 | 0000-00-00 00:00:00 |
| 2 | 1970-01-01 00:01:00 |
+------+---------------------+
2 rows in set (0.00 sec)
