Date range searches give incorrect results

nielsklazenga commented 3 years ago

The ticket in the Support queue is about a search in AVH on eventDate, but I think it might be a general issue with searching on date fields.

We are trying to find records of specimens collected by N.N. Donner on 23 April 1974.

https://avh.ala.org.au/occurrences/search?q=collector_text%3ADonner+AND+occurrence_date%3A%5B1974-04-23T00%3A00%3A00Z+TO+1974-04-24T00%3A00%3A00Z%5D gives no results, while there should be 24.

After a bit of trial and error, I found that https://avh.ala.org.au/occurrences/search?q=collector_text%3ADonner+AND+occurrence_date%3A%5B1974-04-22T14%3A00%3A00Z+TO+1974-04-23T14%3A00%3A00Z%5D gives the expected results.

It turns out that eventDate is stored in SOLR as timestamps for midnight AEST, which is 2 pm the previous day in UTC:

https://biocache-ws.ala.org.au/ws/occurrences/search?q=*%3A*&fq=data_hub_uid%3Adh9&fq=collector_text%3ADonner&fq=eventDate%3A%5B1974-04-22T00%3A00%3A00Z%20TO%201974-04-24T00%3A00%3A00Z%5D&facets=eventDate

  "facetResults": [
    {
      "fieldName": "eventDate",
      "fieldResult": [
        {
          "label": "1974-04-22T14:00:00Z",
          "i18nCode": "eventDate.1974-04-22T14:00:00Z",
          "count": 24,
          "fq": "eventDate:\"1974-04-22T14:00:00Z\""
        }
      ]
    }
  ]

Storing dates without times as UTC timestamps would solve the problem.

djtfmartin commented 3 years ago

This is something to fix in the IndexRecordTransform in pipelines, and I agree with @nielsklazenga suggested remedy.

javier-molina commented 3 years ago

@adam-collins do you have a pointer to the PR for this change?

timhicks-ala commented 2 years ago

Helpdesk ticket: https://support.ehelp.edu.au/a/tickets/110988

@javier-molina , @adam-collins - just following up, was there a fix for this one in the end?

AtlasOfLivingAustralia / la-pipelines

Date range searches give incorrect results #466