背景:服务数据入库的时候报错 com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '2050-04-14 13:43:55' for column 'failure_time' at row 1
鉴于部分入库时间报错,首先排除了代码问题,想到了是不是timestamp时间有范围限制,于是在数据库里也试了试比较大的时间依然报错:
通过搜索得知:timestamp所能存储的时间范围为:'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'。并且受时区影响。
具体原因参考mysql官方文档:https://dev.mysql.com/doc/refman/8.0/en/datetime.html
The
DATE
type is used for values with a date part but no time part. MySQL retrieves and displaysDATE
values in'
format. The supported range isYYYY-MM-DD
''1000-01-01'
to'9999-12-31'
.The
DATETIME
type is used for values that contain both date and time parts. MySQL retrieves and displaysDATETIME
values in'
format. The supported range isYYYY-MM-DD hh:mm:ss
''1000-01-01 00:00:00'
to'9999-12-31 23:59:59'
.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.
其他详细论证文章可参考:https://blog.csdn.net/weter_drop/article/details/89924451
解决方案:将timestamp类型修改为datatime类型。在我的数据库中如下:
/*
证书更新表字段修改 支持入库时间年限增加
*/
-- ----------------------------
-- Table structure for `apply_update`
-- ----------------------------
ALTER TABLE `apply_update`
MODIFY COLUMN `failure_time` datetime NULL DEFAULT NULL COMMENT '证书失效时间' AFTER `effective_time`;
ALTER TABLE `apply_update`
MODIFY COLUMN `effective_time` datetime NULL DEFAULT NULL COMMENT '证书生效时间' AFTER `cert_validity`;