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!