java

Set time to 23:59:59, but database stores next day 00:00:00 — What’s going on?!

Honghao Wang

— 3 min read
Set time to 23:59:59, but database stores next day 00:00:00 — What’s going on?!

1. Problem Description

Yesterday after work, our operations team reported an issue:

Even though the system was configured to lift a blacklist after two days, the prompt was showing three days instead.

Example

  • Today: June 16 — user is blacklisted.
  • Expected: “You can sign up again on June 19.”
  • Actual: June 20.

On investigation, we found the unblocking time had been increased by 1 second:

  • Expected: `2025-06-18 23:59:59`
  • Stored: `2025-06-19 00:00:00`

In the database:

  • About half of the records were correct.
  • The rest defaulted to midnight of the next day.

---

Code Logic

LocalDateTime currentTime = LocalDateTime.now();
LocalDateTime futureTime = currentTime.plus(2, ChronoUnit.DAYS);
// Set to the last second of the day
LocalDateTime resultTime = futureTime.withHour(23).withMinute(59).withSecond(59);
BlackAccount entity = new BlackAccount();
// Entity field type: Date, DB type: timestamp
entity.setDeblockTime(Date.from(resultTime.atZone(ZoneId.systemDefault()).toInstant()));
blackAccountService.save(entity);

Potential issue lies in the above snippet.

---

2. Resolution Process

Investigation Steps

Step 1 — Eliminate Code Conflicts

  • Verified only one place in the project sets `DeblockTime`.
  • Ruled out overwrites from other code.

Step 2 — AI Consultation

Tested code fragment:

LocalDateTime futureTime = currentTime.plus(oaConfigActivityBlacklist.getBlockDay(), ChronoUnit.DAYS);
LocalDateTime resultTime = futureTime.withHour(23).withMinute(59).withSecond(59);
Date date = Date.from(resultTime.atZone(ZoneId.systemDefault()).toInstant());

Q:

> Will `Date` always have `23:59:59`?

---

AI Answer — Key Risks

① Daylight Saving Time (DST)

  • Scenario: If the target date falls on DST adjustment in system timezone, the result may shift.
  • Example: In `Europe/Paris` on Oct 29, 2023, clocks are moved back at 3 AM → shifts may push into the next day.

Using `Date` with system default time zone can lead to:

  • Slips by one second
  • Roll-over into midnight next day

Best Practices:

  • Use UTC consistently.
  • Explicitly define time zones.
  • Validate timestamp before saving.

---

② Time Zone Conversion Issue

  • If `currentTime` is UTC and the system time zone is Asia/Shanghai (UTC+8):
  • The conversion may offset final values.

Example:

  • UTC: `2023-10-01T16:00:00Z`
  • Add 1 day → `2023-10-02T16:00:00Z`
  • Set to `23:59:59` → UTC becomes `2023-10-02T15:59:59Z`

---

Step 3 — Batch Insert Test

Reproduction code:

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);
}

Result:

  • Half records: `2025-06-19 23:59:59`
  • Half records: `2025-06-20 00:00:00`

---

3. Problem Identification

Root cause:

  • Java `Date` → millisecond precision
  • PostgreSQL `timestamp` → default second precision
  • Rounding up occurs when milliseconds ≥ 500, pushing to the next day midnight.

---

4. Solution Proposals

  • Truncate milliseconds before inserting:
  • Use database higher-precision `timestamp`.
   futureTime.withHour(23).withMinute(59).withSecond(59).withNano(0);
  • Store time in UTC and convert only when displaying.
  • Explicitly set `ChronoUnit.SECONDS` before persistence.

---

Adjusting Code Precision

Before:

futureTime.withHour(23).withMinute(59).withSecond(59);

After:

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

---

Adjusting Database Precision

Before:

image

After:

image

---

5. Knowledge Expansion

Java Date vs. LocalDateTime

| Feature | java.util.Date | java.time.LocalDateTime |

|------------------|-----------------------------|--------------------------------------|

| Precision | Milliseconds | Nanoseconds |

| Mutability | Mutable | Immutable |

| Time Zone | Internal UTC, no zone info | No zone info |

---

MySQL `timestamp` vs `datetime`

| Feature | timestamp | datetime |

|------------------|--------------------------------------------------|--------------------------------------|

| Precision | Dependent on definition | Dependent on definition |

| Time Zone | Stored as UTC, converted on retrieval | Stored as literal |

| Range | 1970–2038 | 1000–9999 |

---

Storage Ranges

  • DATETIME: `1000-01-01` → `9999-12-31`
  • TIMESTAMP: `1970-01-01 UTC` → `2038-01-19 UTC`

Precision

  • Both support microseconds with `(6)` definition from MySQL 5.6.4

Storage Size

  • DATETIME: 8 bytes
  • TIMESTAMP: 4 bytes

---

| Scenario | Recommended Type | Reason |

|----------------------------------------|--------------------|------------------------------------------|

| Historical event (order creation) | DATETIME | Not time zone dependent |

| Server local time logs | DATETIME | Reflects server time directly |

| Multi-time-zone app (cross-border) | TIMESTAMP | Automatic time zone conversion |

| Auto timestamp updates | TIMESTAMP | Supports `ON UPDATE CURRENT_TIMESTAMP` |

---

6. Summary

We debugged an issue where stored timestamps often ended 1 second too many:

  • Caused by mismatch between Java `Date` precision and DB `timestamp` precision.
  • PostgreSQL rounded up seconds when milliseconds ≥ 500.

Fix Options:

  • Truncate Java nanoseconds before persisting.
  • Increase DB precision to match Java’s.

Also covered:

  • Differences between Java and DB time types.
  • Where each type is best applied.

Time consistency is critical for any cross-system or multi-platform workload — including global publication scheduling.

Read more