Date equality mismatch due to millisecond rounding differences between SQLite and CloudKit

[lukaskubanek]

Description

When synchronizing Date fields between the local SQLite database (with the default serialization strategy) and CloudKit, Date values that should be identical are often detected as changed in CKRecord.update(with:row:columnNames:parentForeignKey:).

I observed this with Reminder.dueDate in the Reminders example app:

(lldb) p (other.encryptedValues[key]! as Date).timeIntervalSinceReferenceDate  
(TimeInterval) 782983984.64199996  
(lldb) p value.timeIntervalSinceReferenceDate  
(TimeInterval) 782983984.64100003  
(lldb) p other.encryptedValues[key] == value  
(Bool) false  

Although both SQLite and CloudKit appear to serialize Date values with millisecond precision, the repeated conversion introduces rounding errors. As a result, the equality check of Date via bit-exact comparison fails, and the value is mistakenly treated as modified.

Not sure whether the correct solution is to normalize all persisted dates or to compare with a tolerance, but in any case, we should prevent reporting false positives to avoid divergence between the local SQLite and CloudKit databases.

Checklist

• I have determined whether this bug is also reproducible in a vanilla SwiftUI project.
• I have determined whether this bug is also reproducible in a vanilla GRDB project.
• If possible, I've reproduced the issue using the main branch of this package.
• This issue hasn't been addressed in an existing GitHub issue or discussion.

Expected behavior

No response

Actual behavior

No response

Reproducing project

No response

SQLiteData version information

main

Sharing version information

No response

GRDB version information

No response

Destination operating system

iOS 18 / macOS 15

Xcode version information

26.1 β3

Swift Compiler version information

swift-driver version: 1.127.14.1 Apple Swift version 6.2 (swiftlang-6.2.0.19.9 clang-1700.3.19.1)
Target: arm64-apple-macosx15.0

[mbrandonw]

Hi @lukaskubanek, this seems more like a data modeling issue for the user of the library rather than an issue with the library itself. If the user needs precision, then they should know not to use doubles. They should probably use integers. And they can either store this integer directly in their model, or they can cook up a custom Date representation that creates Dates from an Int64 that represents a nanosecond timestamp. That would allow them to use Date's in their model, but in SQLite (and hence CloudKit) it is still stored as an integer.

We even had to deal with this just before releasing the library, where we had to switch from double timestamps to integers.

Since this isn't an issue with the library I am going to convert it to a discussion. Please feel free to continue the conversation over there!

[lukaskubanek]

@mbrandonw Yes, this seems closely related to #183, though not for timestamps indicating when individual fields were modified, but rather directly for Date field values.

While the issue could be avoided by using other data types instead of Date, I would still consider the current behavior incorrect. Since Date equality comparison is used inside the upsert logic in the library, this makes the sync nondeterministic, as it can yield false positives for modifications. Shouldn’t an unmodified value always be treated as such, regardless of its data type?

Let’s consider the following scenario. Device A holds a date in a last known server record, represented as 782983984.64199996 for the comparison purpose. Further, it stores that date value locally, represented as 782983984.64100003 for the comparison purpose. Next, device B nulls-out the date and it gets synced over to device A. Device A sees the removal coming from the server in the upsert logic, but due to the unequal local date values, it interprets it as a concurrent modification on the client, which preserves the “modified” value and syncs it back to device B (which might be hindered due to #274). 💥

Because Date fields stored in SQLite, regardless of the configured storage format, end up as Date fields in CloudKit (at least that’s my understanding), I wonder if it might make more sense to mirror the same serialization strategy across both systems, for example by storing the ISO8601 timestamp as a String in CloudKit instead of a Date. The library then could use the raw values for comparison. Or, perhaps when constructing Date from the same raw value, it would be safe to use its comparison logic directly.