Java8 LocalTime 与MySQL DateTime 精度不同导致数据查询出错

问题描述

在使用 MySQL datetime 进行数据查询的时候只能使用正确的精度才能得出符合期望的数据。并且当使用 datetime 大于精度的时候是进行四舍五入的 。
Datetime 这个日期类型的精度为小数点后面 6 位 。而我们在 查询的时候使用的是LocalDateTime类型的查询条件，该条件精度为小数点后 9 位。
因此导致会查询出超出时间范围的数据，不符合预期。

Java LocalTime 精度

    /**
     * The maximum supported {@code LocalTime}, '23:59:59.999999999'.
     * This is the time just before midnight at the end of the day.
     */
    public static final LocalTime MAX;

查看LocalTime的方法注释，很清楚的写明精度为小数点后9位，最大到.999999999。

LocalDate now = LocalDate.now();    //2024-05-06
LocalDate firstDayOfMonth = now.with(TemporalAdjusters.firstDayOfMonth());//2024-05-01
LocalDateTime firstDayOfMonthStart = firstDayOfMonth.atStartOfDay();//2024-05-01T00:00

LocalDate lastDayOfMonth = now.with(TemporalAdjusters.lastDayOfMonth());//2024-05-31
LocalDateTime firstDayOfMonthEnd = LocalDateTime.of(lastDayOfMonth,LocalTime.MAX);//2024-05-31T23:59:59.999999999

MySQL DateTime 精度

5.7 之后的版本，在默认的秒精确度上，可以带小数，最多带6位小数，即可以精确到 microseconds (6 digits) precision。

Type	Range	Remark
DATETIME	‘1000-01-01 00:00:00.000000’ to ‘9999-12-31 23:59:59.999999’	‘YYYY-MM-DD hh:mm:ss[.fraction]’
TIMESTAMP	‘1970-01-01 00:00:01.000000’ to ‘2038-01-19 03:14:07.999999’	‘YYYY-MM-DD hh:mm:ss[.fraction]’

DATETIME的时间精度为小数点后6位，最大到.999999

查询返回错误结果

如果使用LocalTime作为查询条件代入查询SQL中，由于LocalTime的精度超出了DateTime的精度，这种情况下,假设查询条件是 between 2024-05-01 00:00:00 and 2024-05-31 23:59:59.999999999（结束时间是由框架根据LocalTime.MAX变量自动转化），查询结果将包含 2024-06-01 00:00:00 的数据，这将导致错误的结果发生，因为2024-06-01 00:00:00 的数据不是我们想要的。

解决方案

方案一

可以在调用LocalTime.MAX时手动指定精确到6位：LocalTime.MAX.withNano(999999000);
例如前一个小时的最大时间：

LocalDate now = LocalDate.now();    //2024-05-06
LocalDate firstDayOfMonth = now.with(TemporalAdjusters.firstDayOfMonth());//2024-05-01
LocalDateTime firstDayOfMonthStart = firstDayOfMonth.atStartOfDay();//2024-05-01T00:00
LocalDateTime nextDayOfMonthStart = firstDayOfMonthStart.plusMonths(1);//2024-06-01

LocalDate lastDayOfMonth = now.with(TemporalAdjusters.lastDayOfMonth());//2024-05-31
//2024-05-31T23:59:59.999999999
LocalDateTime firstDayOfMonthEnd = LocalDateTime.of(lastDayOfMonth,LocalTime.MAX);

//2024-05-31T23:59:59.999999
LocalDateTime l1 = firstDayOfMonthEnd.with(LocalTime.MAX).withHour(nextDayOfMonthStart.minusHours(1).getHour()).withNano(999999000);

方案二

在查询时间范围内的数据的时候使用 左关右开 模式也就是：
时间 >= ‘2024-05-01 00:00:00’ 并且 时间 < ‘2024-05-31 23:59:59’

原文链接：
MySQL 由于 Java 日期 LocalDateTime 数据精度引发的线上问题
Java8 LocalTime 与MySQL DateTime 精度不同导致数据查询错误的问题