java.lang.IllegalArgumentException: HOUR_OF_DAY: 0 -＞ 1

Mysql数据查询出现时区问题

环境：mysql5.7， SpringBoot2.2.3版本 ，mybatis，mysql-connector-java 8.0.21，jdk openjdk:8u212-jdk-alpine

出现的问题：

org.springframework.dao.TransientDataAccessResourceException: Error attempting to get column 'csrq' from result set.  Cause: java.sql.SQLException: HOUR_OF_DAY: 0 -> 1 ; HOUR_OF_DAY: 0 -> 1; nested exception is java.sql.SQLException: HOUR_OF_DAY: 0 -> 1 at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:110) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441) at com.sun.proxy.$Proxy109.selectList(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224) at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeFORMany(MybatisMapperMethod.java:173) at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:78) at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148) at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89) at com.sun.proxy.$Proxy144.selectSynDataThree(Unknown Source) at com.voiceai.service.impl.SampleReportServiceImpl.scheduleSynData(SampleReportServiceImpl.java:75) at com.voiceai.service.impl.SampleReportServiceImpl$$FastClassBySprinGCGLIB$$246fe6a3.invoke(<generated>) at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:687) at com.voiceai.service.impl.SampleReportServiceImpl$$EnhancerBySpringCGLIB$$fc30ecf9.scheduleSynData(<generated>) at com.voiceai.common.task.SampleReportTask.executeInternal(SampleReportTask.java:46) at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:75) at org.quartz.core.JobRunshell.run(JobRunShell.java:202) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573) Caused by: java.sql.SQLException: HOUR_OF_DAY: 0 -> 1 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:85) at com.mysql.cj.jdbc.result.ResultSetImpl.getTimestamp(ResultSetImpl.java:914) at com.mysql.cj.jdbc.result.ResultSetImpl.getTimestamp(ResultSetImpl.java:959) at com.alibaba.druid.filter.FilterChainImpl.resultSet_getTimestamp(FilterChainImpl.java:1258) at com.alibaba.druid.filter.FilterAdapter.resultSet_getTimestamp(FilterAdapter.java:1829) at com.alibaba.druid.filter.FilterChainImpl.resultSet_getTimestamp(FilterChainImpl.java:1254) at com.alibaba.druid.proxy.jdbc.ResultSetProxyImpl.getTimestamp(ResultSetProxyImpl.java:740) at com.alibaba.druid.pool.DruidPooledResultSet.getTimestamp(DruidPooledResultSet.java:365) at sun.reflect.GeneratedMethodAccessor266.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.ibatis.logging.jdbc.ResultSetLogger.invoke(ResultSetLogger.java:69) at com.sun.proxy.$Proxy135.getTimestamp(Unknown Source) at org.apache.ibatis.type.DateTypeHandler.getNullableResult(DateTypeHandler.java:39) at org.apache.ibatis.type.DateTypeHandler.getNullableResult(DateTypeHandler.java:28) at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:85) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.createRowKeyForMappedProperties(DefaultResultSetHandler.java:1057) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.createRowKey(DefaultResultSetHandler.java:1019) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.applyNestedResultMappings(DefaultResultSetHandler.java:946) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:434) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForNestedResultMap(DefaultResultSetHandler.java:909) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:326) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:301) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:194) at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65) at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:69) at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325) at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:165) at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:92) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) at sun.reflect.GeneratedMethodAccessor127.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427) ... 16 common frames omitted Caused by: com.mysql.cj.exceptions.WrongArgumentException: HOUR_OF_DAY: 0 -> 1 at sun.reflect.GeneratedConstructorAccessor151.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105) at com.mysql.cj.result.SqlTimestampValueFactory.localCreateFromTimestamp(SqlTimestampValueFactory.java:112) at com.mysql.cj.result.SqlTimestampValueFactory.localCreateFromTimestamp(SqlTimestampValueFactory.java:50) at com.mysql.cj.result.AbstractDateTimeValueFactory.createFromTimestamp(AbstractDateTimeValueFactory.java:87) at com.mysql.cj.protocol.a.MysqlTextValueDecoder.decodeTimestamp(MysqlTextValueDecoder.java:81) at com.mysql.cj.protocol.result.AbstractResultsetRow.decodeAndCreateReturnValue(AbstractResultsetRow.java:87) at com.mysql.cj.protocol.result.AbstractResultsetRow.getValueFromBytes(AbstractResultsetRow.java:241) at com.mysql.cj.protocol.a.result.ByteArrayRow.getValue(ByteArrayRow.java:91) ... 52 common frames omitted Caused by: java.lang.IllegalArgumentException: HOUR_OF_DAY: 0 -> 1 at java.util.GreGorianCalendar.computeTime(GregorianCalendar.java:2829) at java.util.Calendar.updateTime(Calendar.java:3393) at java.util.Calendar.getTimeInMillis(Calendar.java:1782) at com.mysql.cj.result.SqlTimestampValueFactory.localCreateFromTimestamp(SqlTimestampValueFactory.java:108) ... 58 common frames omitted  

线上发现 有一批数据无法从数据库进入队列。在排查数据查询条件无误后，在日志发现查询报错。发现字段“csrq”出现问题，这个字段在数据库里面是
csrq datetime DEFAULT NULL COMMENT ‘出生日期’,
由于无法找到具体原因，直接google
发现 两篇比较有用的文章
链接: MySQL中Date，夏令时时间转换引发的错误HOUR_OF_DAY: 0 -> 1，及解决办法
链接: Spring Data JPA 报 HOUR_OF_DAY: 0 -> 1异常的解决过程和方案

按照文中说的几个时间点，确实存在一个1947-4-15 00:00:00
这个时间段，然后在本地环境测试确实出现。。
测试过程中还有比较奇怪的情况，按照文章中的测试方法

import java.util.Calendar;import java.util.Date;public class Test {    public static void main(String[] args) {            Calendar cal=Calendar.getInstance();            cal.setLenient(false);            cal.set(1947,4,15,0,0,0);            System.out.println(cal.getTime());    }}

我本地的电脑jdk1.8.0_181 在1947-4-15 00:00:00 这个时间不会出现问题，在1947-3-15 00:00:00会出现报错，还有就是在1996-12-01 00:00:00 也会报错
换成jdk 11的版本 也是这样

解决办法

1.线上问题先解决

先将1947-3-15 00:00:00 改到1点， 一点内是没用的哈，比如：00:59:00

2.尝试解决方案

1.按照第一篇和其他网上的说法，已经确实设置了时区serverTimezone=Asia/Shanghai2.尝试过将Date改为LocalDate，但是业务上行不通，数据最后需要转为格式化yyyy-MM-dd HH:mm:ss，用fastJSON和jacksjon都没有办法转为这种格式。转出来的数据不是序列化不对，就是转成了一个分段的json对象。最后根据看到日志中Date class是java.util.Date, 想到java.sql.Date这个类，按照原来的经验这个类是会处理一些特殊的情况。比如 数据库表 create _time  字段为 timestamp 类型，要用java.sql. Timestamp 来接收。

3.最后方案

将java.util.Date 改为 java.sql.Date

改为这个以后测试确实OK了，在对象转jsonString 的时候 1947-4-15 00:00:00 时间默认转成了1947-4-15 01:00:00
，其他时间字段，只要是正常的，转出来以后也是正常的。
原来一直没有注意这个问题，包括很多自动生成工具，如果是时间类型，生成的bean中的Date 都是java.util.Date,看了很多文章，夏令时问题，这个是在9几年没有

来源地址：https://blog.csdn.net/liujia_It/article/details/128313611