LocalTime & MySQL時間精度

Java8 LocalTime 與MySQL DateTime 精度不同導致數據查詢錯誤的問題

Java LocalTime精度

Java 查看LocalTime的方法注釋,很清楚的寫明精度為小數點後9位,最大到.999999999.

    /**
     * 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;

MySQL DateTime精度

MySQL 5.7 之後的版本,在默認的秒精確度上,可以帶小數,最多帶6位小數，即可以精確到 microseconds (6 digits)

Type	Range	Format
DATETIME	'1000-01-01 00:00:00' to '9999-12-31 23:59:59'	'YYYY-MM-DD hh:mm:ss[.fraction]'
TIMESTAMP	'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC

Java 解決方法

// 設定精確到小數第6位
LocalDate.now().atTime(LocalTime.MAX.withNano(999999000));

參考資料

• https://dev.mysql.com/doc/refman/8.0/en/datetime.html

• https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html