Why MySQL DATETIME Misses Milliseconds: Connector Bugs and Practical Fixes

Preface

In recent work I encountered two MySQL timestamp problems: one where mysql‑connector‑java and MySQL had inconsistent precision causing missing data, and another where an application server timezone error prevented data retrieval. This article aims to answer several questions about MySQL timestamps.

Why does MySQL DATETIME precision only support seconds?

Is the MySQL DATETIME type related to timezones?

How should time fields be chosen when designing tables?

Case Analysis: DATETIME Precision Issue

After upgrading mysql‑connector‑java from 5.1.16 to 5.1.30, a regression appeared: queries that used a timestamp condition failed to return results. The root cause is that versions of mysql‑connector‑java prior to 5.1.23 truncate the fractional part of seconds before sending the value to MySQL, which stores DATETIME only to second precision. The newer driver preserves milliseconds, fixing a bug on the driver side but exposing a mismatch with the database.

Three possible solutions were considered:

Change the mapper method parameter type from java.util.Date to java.sql.Date.

Round the timestamp to whole seconds before passing it to the mapper (see image).

Subtract one second from the timestamp before the query.

Evaluation showed that solution 1 discards precision and returns extra rows, solution 3 works but may return one or two unintended rows, and solution 2 effectively compensates for the driver behavior. The final choice was solution 2.

Case Reproduction

Using Homebrew, MySQL 8.0.15 was installed and a user table was created (SQL shown in the image). A Spring Boot + MyBatis project was set up with a user entity, mapper, and MySQL connection configuration (images). A test inserts a row and queries with a timestamp condition, which initially returns no data. After applying the rounding‑to‑seconds logic, the test succeeds and returns the expected row.

During the initial table design, the DATETIME column was defined with fractional seconds (up to 6 digits). This feature was introduced in MySQL 5.6.4, as documented in the official MySQL manual.

Key Takeaways

Match mysql‑connector‑java version with the MySQL server version; using a connector newer than 5.1.23 with MySQL 5.6.4‑ or earlier can cause precision issues.

MySQL provides three time‑related column types: DATE , DATETIME , and TIMESTAMP . A comparison table is shown in the image.

DATETIME stores values as an 8‑byte integer in YYYYMMDDHHMMSS format, independent of timezones.

TIMESTAMP has a smaller range, its displayed value depends on the server, OS, and client timezones.

In most cases, use DATETIME for timestamp fields; avoid storing time as BIGINT.

Avoid using timestamps as query conditions when possible; if required, ensure both MySQL and the application use compatible precision.