The Backfire of MySQL DATETIME Rounding (feat. A Broken 365-Day Streak)

The Problem

In our application, we recently received a furious support ticket from a highly engaged user. They claimed their hard-earned 365-day daily check-in streak had suddenly reset to zero, despite them swearing they clicked the check-in button just before midnight.

Upon investigating the data, the application logs showed they did indeed check in at the last possible fraction of a second. However, when the application attempted to persist the last_check_in_at timestamp with nanoseconds calculated as .999999999, MySQL rounded it up to the next second upon saving, causing the date to inadvertently roll over to the next day.

Root Cause Analysis

MySQL's DATETIME and TIMESTAMP types can store fractional seconds. However, their behavior is determined by the declared fractional seconds precision (known as fsp).

Default Behavior: If the column is simply declared as DATETIME or DATETIME(0), it does not store fractional seconds.
How it Operates: When inserting or updating LocalDateTime data containing nanoseconds from a Java or Kotlin application, MySQL automatically rounds the value to match the column's precision. (It does not simply truncate/drop the decimals!)

Example of the Problematic Value

👉 Application sent: 2026-02-18 23:59:59.999999999
🚨 MySQL DATETIME(0) stored value: 2026-02-19 00:00:00 (Rounding occurred!)

Why Did the Streak Break?

When evaluating daily attendance streaks, our chronological queries often check if a user has a last_check_in_at record occurring on the expected day. Developers frequently use inclusive conditions like check_in_time BETWEEN '2026-02-18 00:00:00' AND '2026-02-18 23:59:59'.

Since the user's last-minute check-in was rounded up to the literal value 2026-02-19 00:00:00 in the database, the check-in count for the 18th queried as zero. Our streak evaluation job ran, noticed the gap for the 18th, and immediately wiped out the 365-day streak.

Solutions

To completely circumvent this "split-second discrepancy", here are three viable strategies to consider:

1. Truncate at the Application Level

Explicitly control the time fraction before passing the value to the DB. You can manually adjust the precision by stripping the nanoseconds or dropping them to the microsecond level prior to saving.

// Kotlin/Java Example
// Remove nanoseconds entirely for safe storage
val safeCheckInTime = LocalDateTime.now().withNano(0) 

// Or explicitly truncate to 6 decimal places (Microseconds)
val truncatedTime = LocalDateTime.now().truncatedTo(ChronoUnit.MICROS)

2. Safely Expand Database Schema Precision

Minimize data loss directly by declaring the schema realistically as DATETIME(3) (milliseconds) or DATETIME(6) (microseconds).

⚠️ Warning: Even in this case, the possibility of rounding at the last digit still exists, meaning it acts more as an enhancement of precision rather than a fundamental solution.

3. Change the Paradigm for Search Logic (Highly Recommended)

Avoid using the inclusive condition (<= 23:59:59) or BETWEEN completely, and adopt the half-open exclusive (<) condition as the safest and most universal approach.

In other words, by designing the queried range as check_in_time >= '2026-02-18 00:00:00' AND check_in_time < '2026-02-19 00:00:00', you ensure an accurate and unified range search across programming languages and DB paradigms, effectively bypassing the rounding risk.

💡 Lessons Learned

"Never blindly trust that the database will innocently store the exact data you hand over to it."

The inherent precision and default rounding/conversion policies of each data type natively supported by the database aren't just mere schema configuration choices. They are critical details capable of instantaneously collapsing the data integrity of an application's core business logic (leading to false streak resets or premature coupon expirations).

Troubleshooting Takeaway Data Integrity Maintained