Why Adding 2 Days Can Yield a 3‑Day Unblock Time: Java Date Precision vs. DB Timestamp

Problem Description

Operations reported that a user black‑list entry set to be released after two days was displayed as three days later. The database stored the unblock time as 2025-06-19 00:00:00 instead of the expected 2025-06-18 23:59:59. Roughly half of the records suffered this one‑second shift.

Investigation Steps

Code Review – Confirmed only one place in the code sets deblockTime. The original logic was:

LocalDateTime currentTime = LocalDateTime.now();
LocalDateTime futureTime = currentTime.plus(2, ChronoUnit.DAYS);
LocalDateTime resultTime = futureTime.withHour(23).withMinute(59).withSecond(59);
BlackAccount entity = new BlackAccount();
entity.setDeblockTime(Date.from(resultTime.atZone(ZoneId.systemDefault()).toInstant()));
blackAccountService.save(entity);

AI Consultation – Asked an AI whether the above code guarantees the final Date has time 23:59:59. The AI highlighted two possible issues:

Daylight‑Saving‑Time (DST) adjustments can shift the instant when the target date falls in a DST transition zone.

Time‑zone conversion problems if the system default zone differs from UTC.

Batch Insertion Test – Inserted 100 records in a loop to reproduce the issue:

for (int i = 0; i < 100; i++) {
    Thread.sleep(100);
    LocalDateTime currentTime = LocalDateTime.now();
    LocalDateTime futureTime = currentTime.plus(2, ChronoUnit.DAYS);
    LocalDateTime resultTime = futureTime.withHour(23).withMinute(59).withSecond(59);
    BlackAccount entity = new BlackAccount();
    entity.setDeblockTime(Date.from(resultTime.atZone(ZoneId.systemDefault()).toInstant()));
    blackAccountService.save(entity);
}

Half of the rows stored 23:59:59, the other half stored 00:00:00 of the next day.

Root Cause

The discrepancy originates from the precision mismatch between java.util.Date (millisecond precision) and PostgreSQL timestamp (second precision). When the millisecond part of the Date is ≥ 500 ms, PostgreSQL rounds up to the next second, turning 23:59:59.500 into 00:00:00 of the following day.

Solutions

Align Java and DB precision – Truncate the nanosecond part before persisting:

// before
futureTime.withHour(23).withMinute(59).withSecond(59);
// after
futureTime.withHour(23).withMinute(59).withSecond(59).withNano(0);

Adjust database column precision – Change the column to a type that stores milliseconds (e.g., timestamp(3)) or use datetime if time‑zone handling is not required.

Knowledge Expansion

Java Date Types

java.util.Date – Millisecond precision, mutable, no time‑zone awareness.

java.time.LocalDateTime – Nanosecond precision, immutable, no time‑zone.

For time‑zone‑aware values, prefer ZonedDateTime or OffsetDateTime.

MySQL/PostgreSQL Date‑Time Types

DATETIME – Stores literal date‑time, no time‑zone, range 1000‑01‑01 to 9999‑12‑31, 8 bytes.

TIMESTAMP – Stores UTC instant, automatically converts to session time‑zone on read, range 1970‑01‑01 to 2038‑01‑19 (unless extended), 4 bytes, supports ON UPDATE CURRENT_TIMESTAMP.

Both types support fractional seconds (e.g., DATETIME(6), TIMESTAMP(6)) for microsecond precision.

Choosing the Right Type

Use DATETIME for historical events or server‑local timestamps where time‑zone conversion is undesired.

Use TIMESTAMP for multi‑region applications that need automatic UTC handling.

When sub‑second precision is required, select the (6) variant of either type.

Conclusion

The unexpected three‑day unblock date was caused by a one‑second overflow when a millisecond‑precise java.util.Date was stored in a second‑precise timestamp column. By either truncating the nanosecond component in Java or adjusting the column’s precision, the problem is resolved. The article also compares Java date‑time APIs with database types and offers guidance on selecting the appropriate type for different scenarios.