Date Queries do not return correct results

[timritzer]

When querying on date properties, the queries do not properly handle timezone information, and seem to treat passed in dates as string comparison instead of a date. Also BETWEEN may have boundary case issues even with UTC comparisons.

Three test cases included showing the issues:
"TestCase1Date": "2018-10-23T00:00:00.000-0500", //Next Day, shouldn't be in range
"TestCase2Date": "2018-10-22T05:00:00", //Start of day, should be in range
"TestCase3Date": "2018-10-23T04:59:59Z", //End of day (In UTC), should be in range

Searching for anytime during a specific day (Today for me)
(10/22/2018 00:00:00 CDT) (-0500)
through
(10/22/2018 11:59:59 CDT) (-0500)

Using Between for filtering like so:
.where(Expression.property("TestCaseXDate")
.between(Expression.date(startOfRange), and: Expression.date(endOfRange)))

Results:
TestCase1Date is not considered in range, and should be.
TestCase2Date is considered in range, and should not be.
TestCase3Date is not considered in range, and should be.

Attached app will need the CouchbaseLiteSwift framework copied back in, it made the zip too large to upload.

CouchbaseTestApp.zip

---

• Version: Couchbase Lite 2.1
• Client OS: iOS 12.0
• Server: N/A (No Sync Required for recreation)

[snej]

The short answer: Time zones in dates are not supported. Always encode dates in UTC.

The long answer: Documents are JSON. JSON does not have a Date type, so dates are encoded as strings. There is nothing in a document to distinguish dates from other strings, so queries can only compare them as strings. Fortunately ISO-8861 dates do compare correctly as strings ... but only if the time zones match.

[pasin]

In addition to @snej's comment, the MutableDocument/MutableDictionary.setDate(date, key) (supported by all platforms including Android and .NET) can be used and the method will convert the date into ISO-8861 using UTC format.

[timritzer]

That is simply not acceptable. The old version offered tools to compare correctly, the new version does not. We are a EE customer and I have filed a support ticket.

We need to retain the created timezone information for all new data so we cannot simply use UTC.
And for other fields we are not working off of a homogeneous data source where we have any control over the created data, so we cannot turn dates we do not know time zones (old information) into UTC.

I realize JSON does not have a date type. But we are using ISO date formats, which is a gold standard of how to encode for JSON.

And even when encoding as a UTC date, the between query does not return correct results. The example is attached.

Server N1QL handles this all just fine. The old version handled this just fine. We are repeatedly told to upgrade and yet basic functionality simply doesn't work.

[timritzer]

@pasin in the real app we are actually syncing from server, so we cannot mess with the data to change it on device side, it is coming from Sync Gateway. I was just making the most simple recreation possible.

[timritzer]

@snej The example above shows UTC not even working correctly

[snej]

Server N1QL has date/time functions that you can use to perform date comparisons and date/string conversion ... we don't have those yet in mobile 2.x. Without such explicit casts, the query engine has no idea whether a JSON string is supposed to be a date or not.

Your test app generates the start/end dates programmatically but doesn't specify a time zone:

        if let startOfRange = dateFromComponents(2018, month: 10, day: 22, hour: 0, minute: 0, second: 0),
            let endOfRange = dateFromComponents(2018, month: 10, day: 22, hour: 23, minute: 59, second: 59) {

The result is that the date will be in the local time zone of where the app is run; but when CBL converts the date to JSON for the query it converts it to UTC. The results of this will vary depending on where you are, but I'm pretty sure that's what's causing the unexpected results.

[snej]

This is covered by #2220. It's not a platform bug.

[timritzer]

That is how dates work in Swift. There isn't a timezone, it is always stored as UTC under the covers. The init only serves to tell it how to adjust it.

How do we query against this existing data? I haven't seen a workaround yet that is feasible for us. I have no tools to treat it as a date in a N1QL query, and I cannot change the date formats.

How do I query based on dates in mobile 2.X with those constraints?

[JFlath]

Hi All,

Stepping in here - I think it's clear that there's a lot of context and background that isn't best captured or discussed in a GitHub Issue. As we've already got other channels of communication open, I'd like to suggest we fall back to those for the time being. @timritzer, I'll follow up with you directly and also with our internal teams (@snej, @pasin, et al.) to see how we can best come to a solution here. From there we can clear up anything needed on the product side with this or other GitHub Issues.

Thanks,

James