Java LocalDateTime 與 MariaDB TIMESTAMP 精度差異引發查詢問題

問題

資料庫儲存每小時的資料
程式查詢一日的資料本該只有 24 筆
結果卻多出一筆

LocalDateTime dateL = LocalDateTime.parse(date, DateUtil.FORMATTER);
LocalDateTime startDateL = dateL.toLocalDate().atStartOfDay(); //2024-08-09T00:00
LocalDateTime endDateL = dateL.toLocalDate().atTime(LocalTime.MAX); //2024-08-09T23:59:59.999999999

原因

傳入的參數 LocalTime.MAX 支援到奈秒（nanosecond，10 ⁻ ⁹ 秒）
而 MariaDB 資料庫 TIMESTAMP 欄位預設只有到微秒（microsecond，10 ⁻ ⁶ 秒）
多出來的就被自動進位至 '2024-08-10 00:00:00'
造成撈到隔一天的資料

這個查詢等價於

SELECT *
FROM my_table t
WHERE t.date_time
  BETWEEN '2024-08-09 00:00:00'
  AND '2024-08-09 23:59:59.999999'

解決

將查詢的參數設定成微秒

LocalDateTime endDateL = dateL
                            .toLocalDate()
                            .atTime(LocalTime.MAX)
                            .truncatedTo(ChronoUnit.MICROS);

結論

要注意資料庫與程式的時間精度（time precision）是否相吻合