Date parsing error in Elasticsearch 7.1

Elastic Stack Elasticsearch
1

We are using the AWS Elasticsearch 7.1 and we see some date formats are not being supported. It used to work in 5.5 and its a issue with only 7.1.

Mapping :

           "dynamic": "strict",
           "dynamic_templates": [],
           "properties": {
               "_all": {
                   "type": "text"
               },
               "age": {
                   "type": "long"
               },
               "endDateTime": {
                   "type": "date",
                   "format": "yyyy-MM-dd'T'HH:mm:ss.SSS'ZZ'||yyyy-MM-dd'T'HH:mm:ss.SSS||yyyy-MM-dd'T'HH:mm:ss.SSSXXX"
               },
               "id": {
                   "type": "keyword",
                   "copy_to": [
                       "_all"
                   ],
                   "ignore_above": 256
               },
               "name": {
                   "type": "keyword",
                   "fields": {
                       "default": {
                           "type": "text",
                           "analyzer": "standard"
                       },
                       "language": {
                           "type": "text",
                           "analyzer": "english"
                       }
                   },
                   "copy_to": [
                       "_all"
                   ],
                   "ignore_above": 256
               }
           }
       }
   }

Query:

  "from": 0,
  "size": 10,
  "query": {
    "bool": {
      "must": [
        {
          "constant_score": {
            "filter": {
              "range": {
                "endDateTime": {
                  "from": "2020-07-08T23:48:21.530-07:00",
                  "to": null,
                  "include_lower": false,
                  "include_upper": true,
                  "boost": 1
                }
              }
            },
            "boost": 1
          }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1
    }
  }
}

The error is :
"root_cause": [
{
"type": "parse_exception",
"reason": "failed to parse date field [2020-07-08T23:48:21.530-07:00] with format [yyyy-MM-dd'T'HH:mm:ss.SSS'ZZ'||yyyy-MM-dd'T'HH:mm:ss.SSSXXX]: [Text '2020-07-08T23:48:21.530-07:00' could not be parsed, unparsed text found at index 23]"
}
],
"type": "search_phase_execution_exception",
"reason": "all shards failed",
"phase": "query",
"grouped": true,

Can anyone please help here?

2

@sharadha_s
I don't know the exact reason. But if you change order of patterns the query works.

yyyy-MM-dd'T'HH:mm:ss.SSSXXX||yyyy-MM-dd'T'HH:mm:ss.SSS'ZZ'||yyyy-MM-dd'T'HH:mm:ss.SSS

Even more surprising, if you set "include_lower" to true, your current format works too.

3

Yes ordering works . But what if we want to add some more custom date formats to the front and we want to search against them? This doesn't seem to work for all the date formats.

4

I am guessing here. It may have something to do with yyyy-MM-dd'T'HH:mm:ss.SSS pattern being subset of yyyy-MM-dd'T'HH:mm:ss.SSSXXX. Put more restrictive pattern first.

Also you should regression for all types of dates when adding a new pattern.

5

I can't test at the moment but I am pretty certain it's the offset (-07:00) that's causing the problem. I think range queries expect the time in UTC instead of a localised format.

Try removing the offset from the timestamp and using the time_zone attribute of the range query. There's an example here.

Dates are awkward!

6

We use both range and term queries for the dates depending on the requirement. So does it work for both?

7

make sense. But the issue is if we want to search with multiple date formats, how we define this order. Lets i say i have a format defined in this order : [ "yyyy-MM-dd'T'HH:mm:ss.SSSXXX","yyyy-MM-dd'T'HH:mm:ss.S", "yyyy-MM-dd'T'HH:mm:ss.SS" ]. I want to search against "2020-07-08T23:48:21.530-07:00" or search against "2020-02-04T15:16:03.01". In this case first one will pass as it will match the first format and second one fails which will match the second format.

8

@sharadha_s
swapping patterns 2 and 3 will allow you to specify date in any of the three formats.

9

thanks @Vinayak_Sapre . Is this ordering changed in 7.1? As it used to work for us in previous releases.

10

@sharadha_s
ES switched from Joda time to Java time in 7.0. https://www.elastic.co/guide/en/elasticsearch/reference/7.x/migrate-to-java-time.html

Not sure if this is the root cause of your issue because sample patterns in the guide do not align with my theory. I strongly recommend validating all types of dates.

11

yes it totally make sense to me. Thanks for sharing it!! really appreciate it.

12

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.