Java LocalDateTime conversioning with mybatis

Question

Environment

• mybatis-spring-boot-starter 2.0.1
• mybatis 3.5.1
• mybatis-spring 2.0.1
• mysql-connector-java 8.0.16

jvm timezone : Asia/Seoul

---

My test query

SELECT #{from}, #{to}

Input

YearMonth input = YearMonth.of(year, month)
LocalDateTime from = input.atDay(1).atStartOfDay()
LocalDateTime to = input.atEndOfMonth().atTime(LocalTime.MAX)

---

Case1. add serverTimezone=UTC into jdbc url

Case1. add serverTimezone=Asia/Seoul into jdbc url

[11:03:54] [main] [DEBUG] BaseJdbcLogger.debug(143)| ==>  Preparing: SELECT ?, ? 
[11:03:54] [main] [DEBUG] BaseJdbcLogger.debug(143)| ==> Parameters: 2020-01-01T00:00(LocalDateTime), 2020-01-31T23:59:59.999999999(LocalDateTime)
[11:03:54] [main] [DEBUG] Slf4jSpyLogDelegator.sqlTimingOccurred(368)|  com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
1. SELECT 2020-01-01T00:00, 2020-01-31T23:59:59.999999999
 {executed in 3 msec}
[11:03:54] [main] [INFO ] Slf4jSpyLogDelegator.resultSetCollected(610)| 
|----------------------|----------------------|
|2019-12-31 15:00:00.0 |2020-01-31 15:00:00.0 |
|----------------------|----------------------|
|----------------------|----------------------|

[14:27:16] [main] [DEBUG] BaseJdbcLogger.debug(143)| ==>  Preparing: SELECT ?, ? 
[14:27:16] [main] [DEBUG] BaseJdbcLogger.debug(143)| ==> Parameters: 2020-01-01T00:00(LocalDateTime), 2020-01-31T23:59:59.999999999(LocalDateTime)
[14:27:16] [main] [DEBUG] Slf4jSpyLogDelegator.sqlTimingOccurred(368)|  com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
1. SELECT 2020-01-01T00:00, 2020-01-31T23:59:59.999999999
 {executed in 4 msec}
[14:27:16] [main] [INFO ] Slf4jSpyLogDelegator.resultSetCollected(610)| 
|----------------------|----------------------|
|2020-01-01 00:00:00.0 |2020-02-01 00:00:00.0 |
|----------------------|----------------------|
|----------------------|----------------------|

---

Q1. Is java LocalDateTime converted to jdbc TIMESTAMP with mybatis LocalDateTimeTypeHandler?

Q2. Does mybatis LocalDateTimeTypeHandler convert value based on jdbc servertimezone setting?

Q3. Why my 2020-01-31T23:59:59.999999999 value converted to 2020-02-01 00:00:00.0?

---

Answer 1

• A1. No, MyBatis does not convert LocalDateTime to java.sql.Timestamp since version 3.5.1 (it did in the earlier versions, though).
• A2. No, MyBatis just passes the value to the driver. You can check the type handler implementation yourself.
  It is actually the driver that performs the conversion and the behavior is filed as a bug on their tracker.
  https://bugs.mysql.com/bug.php?id=93444
• A3. While Java's LocalDateTime has nanoseconds (9 digits) precision, MySQL's TIME, DATETIME and TIMESTAMP only have microseconds (6 digits) precision. And the behavior you observed is explained in the MySQL documentation.

  Inserting a TIME, DATE, or TIMESTAMP value with a fractional seconds part into a column of the same type but having fewer fractional digits results in rounding.