[总结]mysql 插入dateTime 2020.01.13 23:59:59 最终值是2020.01.14 00:00:00

问题描述

今天发现一个奇怪的现象，在笔者的某个业务场景中，需要存储一个时间，对于时间的存储，我们一般期望开始时间是2020.01.13 00:00:00 结束时间我们期望是2020.01.13 23:59:59

但是在实际存储却变成了2020.01.14 00:00:00

于是经过一番debug，终于定位到问题。

排查分析

生成指定日期最大时间点代码

/**
 * 获得某天最大时间 2017-10-15 23:59:59.999
 * @param date
 * @return
 */
public static Date getEndTimeOfDay(Date date) {
    LocalDateTime localDateTime = LocalDateTime.ofInstant(Instant.ofEpochMilli(date.getTime()), ZoneId.systemDefault());;
    LocalDateTime endOfDay = localDateTime.with(LocalTime.MAX);
    endOfDay.withNano(0);
    return Date.from(endOfDay.atZone(ZoneId.systemDefault()).toInstant());
}

insert语句

[EAP] 2020-01-13 16:36:38.713 [DEBUG] [DubboServerHandler-10.10.30.94:20884-thread-199] com.jlb.eap.vacation.dao.VacationInfoMapper.insert.debug:143 - ==>  Preparing: INSERT INTO eap_vacation_info ( id,lesson_id,start_time,end_time,type,vacation_value,learner_id,class_id,gmt_create,gmt_modified,real_end,status,real_value,vacation_reason,teacher_uid,operator_uid,curriculum_id,extend,deleted,creator,modifier,request_source,role_type ) VALUES( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )

 

[EAP] 2020-01-13 16:36:38.714 [DEBUG] [DubboServerHandler-10.10.30.94:20884-thread-199] com.jlb.eap.vacation.dao.VacationInfoMapper.insert.debug:143 - ==> Parameters: 15163469(Long), null, 2020-01-13 00:00:00.0(Timestamp), 2020-01-13 23:59:59.999(Timestamp), 0(Integer), 2(Integer), 84971597(Long), 26705989(Long), 2020-01-13 16:36:23.848(Timestamp), 2020-01-13 16:36:24.521(Timestamp), null, 0(Integer), null, 1(Integer), 69d90209551c422a82daec4d898a0d85(String), 6248756da3504c9c9638b75d789bb509(String), 410931277(Long), {"reason":"0","reasonDes":"测试请假时间"}(String), 0(Integer), 10313668(Long), 10313668(Long), 0(Integer), 1(Integer)

注意日志中：

 2020-01-13 00:00:00.0(Timestamp), 2020-01-13 23:59:59.999(Timestamp)

 

实际存储到mysql后变为

 

修复后

 

 

解决问题

/**

 * 获得某天最大时间 2017-10-15 23:59:59.000

 * @param date

 * @param withZeroNano  等于true 2017-10-15 23:59:59.000 ；等于false = 2017-10-15 23:59:59.999

 * @return

 */

public static Date getEndTimeOfDay(Date date, boolean withZeroNano) {

    LocalDateTime localDateTime = LocalDateTime.ofInstant(Instant.ofEpochMilli(date.getTime()), ZoneId.systemDefault());

    LocalTime localTime = null;

    if (withZeroNano){

        localTime = LocalTime.of(23, 59, 59);

    }else {

        localTime = LocalTime.MAX;

    }

    LocalDateTime endOfDay = localDateTime.with(localTime);

    return Date.from(endOfDay.atZone(ZoneId.systemDefault()).toInstant());

}