MySQL中timestamp类型支持范围

最近项目中App端调用接口新增数据时，发现插入失败，查看日志发现报错

Cause: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '1970-01-01 08:00:00' for column 'lastModifyTime' at row 1

原来是lastModifyTime字段插入的值不支持，但这个字段一般是不需要前台传入的，可以在MySQL中设置该字段默认为当前时间，并根据当前时间戳更新。但是仔细看报错信息，1970-01-01 08:00:00这个时间明明看起来在格式上没有任何问题啊，为什么会插入不了呢？排除了其他可能的原因后，怀疑可能是这个值的问题，查阅MySQL的官方文档，可以看到其中对于timestamp类型的说明

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

原来timestamp支持的范围是1970-01-01 00:00:01到2038-01-19 03:14:07，那为什么1970-01-01 08:00:00看起来是在这个范围内却不支持呢，继续往下看

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable. For more information, see Section 10.6, “MySQL Server Time Zone Support”.

MySQL文档中支持的时间范围后面都添加了UTC，说明这个时间是和时区相关的，MySQL在存储的时候会将timestamp类型的字段从当前时区转成UTC时区。我们可以查看一下当前时区

show variables like "%time_zone%";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+

从当前时区转成UTC时区需要减去8小时，所以最后1970-01-01 08:00:00这个时间存储到MySQL其实变成了1970-01-01 00:00:00，不在timestamp类型的范围内了~

参考：MySQL的timestamp字段可以使用的范围是多少