MySQL Rounds Your Timestamps

MySQL's DATETIME defaults to second precision, while Java's LocalDateTime is precise down to nanoseconds. When you write a timestamp with milliseconds into MySQL, anything beyond the column's precision gets rounded — and the stored time can end up a full second later than what you sent.

The Symptom

Say the Java side has 2022-01-01 12:34:56.789. After writing it into a plain DATETIME column, it reads back as 2022-01-01 12:34:57 — the milliseconds got rounded up into the seconds.

The discrepancy is easy to miss day-to-day, but it surfaces in a few scenarios:

1. A unit test saves an entity, reads it back, and compares the time field with assertEquals — and fails intermittently. The carry only happens when the milliseconds are 500 or above, so the problem comes and goes.

2. Cursor pagination or range queries on a creation-time column count a boundary record twice, or skip it entirely.

3. The timestamp in your logs and the one in the database differ by a second, and nothing lines up when you're tracing a request.

Why It Happens

Since MySQL 5.6.4, DATETIME, TIMESTAMP, and TIME all support fractional seconds. The precision is set by the fsp (fractional seconds precision) in the type definition, ranging from 0 to 6, with a default of 0 — seconds only.

The key behavior: when an inserted value has higher precision than the column, MySQL rounds per the SQL standard rather than truncating, and by default emits no warning at all. So .789 carries into the seconds, while .400 is silently dropped — either way, the stored value no longer matches what Java has in memory.

On the Java side, LocalDateTime stores time internally in nanoseconds, and the JDBC driver passes the fractional seconds along to the server, so the rounding happens on MySQL's end. java.sql.Timestamp also has nanosecond precision — switching the Java type to it doesn't avoid the database-side rounding. The root of the problem is the column's precision definition.

Fixes

1) Raise the column precision

If your use case needs millisecond precision (enough for the vast majority of created/updated timestamp scenarios), just define the column as DATETIME(3):

ALTER TABLE t_order
  MODIFY create_time DATETIME(3) NOT NULL;

Use DATETIME(6) if you need microseconds. Note that the matching default-value functions need a precision argument too: NOW() only goes to seconds, so write NOW(3) or CURRENT_TIMESTAMP(3), otherwise the default value itself has no fractional seconds.

2) Truncate ahead of time on the Java side

If you'd rather not touch the schema, align the precision to seconds before writing:

// Drop everything below seconds, matching DATETIME(0) precision
LocalDateTime now = LocalDateTime.now().truncatedTo(ChronoUnit.SECONDS);

Now the in-memory value and the stored value are identical, and comparisons and assertions never drift. Handling this once in a shared entity base class or audit-field populator is enough.

Gotchas

• The rounding carries up rather than zeroing out. Many people instinctively assume extra precision gets chopped off; it's actually rounded, which is exactly where the "time gained a second" weirdness comes from. MySQL 8.0 offers the TIME_TRUNCATE_FRACTIONAL sql_mode to switch the behavior to truncation, but it affects behavior globally — evaluate existing workloads before flipping it.
• After changing to DATETIME(3), all existing rows have .000 fractional seconds. Keep that in mind when sorting a mix of old and new data.
• Higher fsp costs more storage. Pick what you need — there's no reason to default everything to DATETIME(6).

Wrapping Up

The "extra second" is fundamentally a precision mismatch: Java sends nanoseconds, the MySQL column only stores seconds, and the excess gets rounded. Two ways out — either change the column to DATETIME(3)/DATETIME(6) so the database can hold it, or truncate on the Java side with truncatedTo(ChronoUnit.SECONDS) up front. There's only one principle: make the precision in memory match the precision in the column definition, so what you write is what you read.