Sanity: GROQ: Datetime string comparisons are occasionally inaccurate

[robinpyon]

Describe the bug

• Comparing two ISO 8601 datetime strings often returns incorrect values
• Anecdotally, it feels like minute / second values are often being completely ignored
• Return values from datetime comparisons can vary when used in a filter
• Ordering values can also be inconsistent dependent on whether naked projections are used or not

To Reproduce

Create a simple schema with the following data:

one 'author' entry
- title: string = 'John Smith'

one 'article' entry
- date: string = "2019-07-17T16:00:00Z"
- author: reference = ('John Smith')

All following snippets are GROQ queries directly run in Vision:

1. Ignoring second / millisecond values

{
  // Second comparison fails: returns false
  "01": "2019-07-17T16:00:10Z" > "2019-07-17T16:00:00Z",
  // Minute comparison fails: returns false
  "02": "2019-07-17T16:10:00Z" > "2019-07-17T16:00:00Z",
  // Hour comparison passes: returns true   
  "03": "2019-07-17T17:00:00Z" > "2019-07-17T16:00:00Z",  
}

2. Return values from datetime comparisons can also vary when used in a filter

{
  // Minute comparison fails: returns nothing
  "01": *[
    "2019-07-17T16:01:00Z" > "2019-07-17T16:00:00Z"
  ] { 
    date
  } [0],

  // Minute comparison passes and returns an object
  // Note that the `date` in the returned object is equivalent to the string above.
  "02": *[
    "2019-07-17T16:01:00Z" > date
  ] { 
    // This is the exact same comparison in the filter, but returns false!
    "comparisonTest": "2019-07-17T16:01:00Z" > date  
    // Returns true
    "equalityTest": date == "2019-07-17T16:00:00Z",
    // Returns 2019-07-17T16:00:00Z
    date,       
  } [0]
}

3. There are also inconsistencies with ordering, depending on whether you use naked projections or not

// Naked projection + order returns incorrect results
*[][0] {
  "articles": *[_type == 'article'].date | order(date asc)   
}

// Returns:
// "articles": [
//   "2019-07-17T16:00:00Z",
//   "2019-07-17T16:05:00Z",
//   "2019-07-17T16:00:50Z",
//   "2019-07-17T16:25:00Z"
// ]

// However, a 'regular' projection + order returns correct results
*[_type == 'article'] {
  date
} | order(date asc)

// Returns:
// [
//   {
//     "date": "2019-07-17T16:00:00Z"
//   },
//   {
//     "date": "2019-07-17T16:00:50Z"
//   },
//   {
//     "date": "2019-07-17T16:05:00Z"
//   },
//   {
//     "date": "2019-07-17T16:25:00Z"
//   }
// ]

Expected behavior

Datetime string comparisons to work reliably regardless of whether you're doing minute and second comparison, using naked projections or nested subqueries.

Which versions of Sanity are you using?

@sanity/cli              0.144.0 (up to date)
@sanity/base             0.144.1 (up to date)
@sanity/components       0.144.2 (up to date)
@sanity/core             0.144.0 (up to date)
@sanity/default-layout   0.144.2 (up to date)
@sanity/default-login    0.144.0 (up to date)
@sanity/desk-tool        0.144.2 (up to date)
@sanity/rich-date-input  0.144.0 (up to date)
@sanity/vision           0.144.2 (up to date)

What operating system are you using?

Mac OSX 10.15

Which versions of Node.js / npm are you running?

v10.16.0
6.9.0

Additional context

I believe these datetime inconsistencies are critical issues, especially when working with time sensitive content. If you're using the now() function to determine whether content should be made visible, currently those entries won't be made visible until at least an hour has passed.

Possibly related issues

String ordering inconsistencies: (#1509)
Logical AND operator inconsistencies: https://sanity-io-land.slack.com/archives/C9Z7RC3V1/p1572460708406200

[evenwestvang]

Thank you for reporting @robinpyon – we will get back to you on this as soon as we can. We have a possible solution that we are vetting.

[robinpyon]

Hate to belabour the issue here, but has there any progress on this front @evenwestvang?

Unfortunately the inconsistencies with now() / date comparisons are becoming a bit of a blocker for us. Comparisons such as 2020-01-23T01:30:00.000000Z > 2020-01-23T00:30:00.000Z that return incorrect values are a significant pain point.

For a particular project we use now() _very_ liberally – combined with a private dataset – to selectively show time sensitive content. Refactoring out all instances of now() and doing that server side is a big task, since we also do all our pagination in GROQ (which in turn, relies on accurate query responses in order to be meaningful).

[simen]

We are working on a major overhaul of the query engine which will fix this problem which is related to string collation and not actually dates. We will not change this behavior in the current version of GROQ since it may have unintended consequences that may break code out there.

However, for your specific problem it should be enough to wrap your date strings in the dateTime() function to let GROQ know you want the strings handled as timestamps.

[simen]

I realize this function is not documented. Will remedy!

[simen]

Documentation: https://www.sanity.io/docs/groq-functions#datetime-af1b4fe06624

[robinpyon]

Thank you @simen – dateTime() did the trick!

In my case (using rich dates), either of the following queries worked for me (colaesce or defined are used to prevent calling dateTime() on null)

Fetch all published (non-draft), documents of type _article_ that are published in the past:

*[
  _type == 'article'
  && defined(publishDate.utc)
  && dateTime(publishDate.utc) < dateTime(now())
  && !(_id in path("drafts.**"))
] {
  ...
}

*[
  _type == 'article'
  && dateTime(coalesce(publishDate.utc, '')) < dateTime(now())  
  && !(_id in path("drafts.**"))
] {
  ...
}