Date_time_exception: Invalid value for MonthOfYear (valid values 1 - 12): 28

Elastic Stack Elasticsearch
1

I am using ES 7.10.0
In my case I expect date values like:

Case1: 28/09/2022:02:00:00
Case2: 09/28/2022:02:00:00
Case3: 2022-09-28T02:00:00

Below is the template, which I am using:

PUT _template/template_1
{
  "index_patterns": ["test*"],
  "mappings": {
    "_source": {
      "enabled": true
    },
    "properties": {
      "created_at": {
        "type": "date",
        "format": "dd/MM/yyyy:HH:mm:ss||MM/dd/yyyy:HH:mm:ss||strict_date_optional_time"
      }
    }
  }
}

The above mapping works for Case1 and Case3 but it throws an exception for Case2 due to which the multi-format date thing does not gets supported.

Here is my Input

PUT test1/_doc/1
{
  "created_at": "28/09/2022:02:00:00"
}

PUT test1/_doc/2
{
  "created_at": "09/28/2022:02:00:00"
}

PUT test1/_doc/3
{
  "created_at": "2022-09-28T02:00:00"
}

The Error Message:

{
  "error": {
    "root_cause": [
      {
        "type": "date_time_exception",
        "reason": "date_time_exception: Invalid value for MonthOfYear (valid values 1 - 12): 28"
      }
    ],
    "type": "mapper_parsing_exception",
    "reason": "failed to parse field [created_at] of type [date] in document with id '3'. Preview of field's value: '09/28/2022:02:00:00'",
    "caused_by": {
      "type": "date_time_exception",
      "reason": "date_time_exception: Invalid value for MonthOfYear (valid values 1 - 12): 28"
    }
  },
  "status": 400
}

So for whichever date format (either Case1 or Case2) is given first in the mappings, ES supports it and for the other one it creates an exception resulting in multi-format feature not to work

Is there any workaround for this from the mapping perspective?
Kindly help with the same, Thanks.

2

It looks like a bug to me. Could you please open an issue and provide the same great description you did here?

Thanks!

3

If you expect to handle date values like that, what do you expect the date value to be for the following?

Case 4: 08/09/2022:02:00:00

Should it be parsed the same as Case 1 or Case 2?

2 Likes
4

Very true. I'd call it a random date. Let's hope aggregations or searches are only on years :wink:

5

True enough.

Do you all think there could be any solution for this?

One more query:
Let's say these formats are decided by some other key value condition in the record, so based on that can we apply the different date formats in the mappings ?

6

If you can tell based on other fields I would recommend transforming the date to a common format before indexing it. You can not use other fields in mappings.

1 Like
7

Thanks a lot @Christian_Dahlqvist, @dadoonet for all the help

Btw, what do you guys feel about this, are we expecting any new feature upcoming with regards Template/Mapping which gives possibility like changing field_types or formats (date formats etc). based on some (Key value) conditions at the template/mapping level (may be by using painless script). So that it gives complete control from template end as well?

8

Given that analysis is performed at indexing as well as query time I think any fundamental change like that is quite unlikely. The correct way is to clean up your data before indexing it.

1 Like
9

Thanks again @Christian_Dahlqvist . And in the case if we don't have an other differentiating (field value pair ) for the different date formats, then we won't be able to reach to a proper solution, right?

10

If you can not tell how the timestamp is to be interpreted, how would Elasticsearch be able to handle it? If you do not have anything in the data itself you may need to reformat/interpret based on the source of the data and your knowledge of which format it uses.

1 Like
11

I think it might be possible with runtime fields...

1 Like
12

Good point. That may work as long as there is another field to trigger on or the format is unambigous. That would require upgrading to a newer version though, wouldn't it?

13

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