Dynamic date formats: German date format results in string field

[spinscale]

Elasticsearch version (bin/elasticsearch --version): 5.5.0

Description of the problem including expected versus actual behavior: Using dynamic_date_formats with a german date and a german format with dots results in the date being stored as a string instead of a date

Steps to reproduce:

DELETE my_index

PUT my_index
{
  "mappings": {
    "my_type": {
      "dynamic_date_formats": ["dd/MM/yyyy", "dd.MM.yyyy"]
    }
  }
}

PUT my_index/my_type/1
{
  "date1": "25.09.2015",
  "date2": "25/09/2015"
}

GET my_index/_mapping

The mapping now returns

{
  "my_index": {
    "mappings": {
      "my_type": {
        "dynamic_date_formats": [
          "dd/MM/yyyy",
          "dd.MM.yyyy"
        ],
        "properties": {
          "date1": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "date2": {
            "type": "date",
            "format": "dd/MM/yyyy"
          }
        }
      }
    }
  }
}

[jimczi]

Prior to 6 dynamic date detection relies on a set of hardcoded characters so only strings that contain :, - or / are tested against the provided date formats.
It's fixed in 6 by this pr: #22171 that removes the limitation and test all strings that are not parseable as longs or doubles.
We should probably document this behavior for versions prior to 6.

[heinzmeise]

It works now as expected in 6, Dates separated by a Dot(.) are recognized if "dynamic_date_formats" contains a proper pattern.

However, if I add a particular Format to the mapping, the default date detection fails to recognize a date such as "2015/09/02". Without "dynamic_date_formats" set, this date is automatically recognized.

Does that mean that adding a custom format overrides the default date detection?

Steps to reproduce

create an empty Index, add 4 different dates / timestamps

PUT my_index
POST my_index/type/
{
  "date1": "2015/09/02",
  "timestamp1": "2017/12/19 13:16:21",
  "german_date1": "01.01.2018",
  "german_timestamp1": "01.01.2018 13:46:58"
}

now, update the mapping by adding dynamic_date_formats....

PUT my_index/_mapping/type
{
     "dynamic_date_formats": ["dd.MM.yyyy", "dd.MM.yyyy HH:mm:ss"]
}

...next, add 4 new fields containing the same dates / timestamps

POST my_index/type/
{
  "date2": "2015/09/02",
  "timestamp2": "2017/12/19 13:16:21",
  "german_date2": "01.01.2018",
  "german_timestamp2": "01.01.2018 13:46:58"
}

GET my_index/_mapping

The mapping now contains 8 Fields, each field is either of type Text or Date, depending on when the Field has been added (before / after adding dynamic_date_formats):

{
  "my_index": {
    "mappings": {
      "type": {
        "dynamic_date_formats": [
          "dd.MM.yyyy",
          "dd.MM.yyyy HH:mm:ss"
        ],
        "properties": {
          "date1": {
            "type": "date",
            "format": "yyyy/MM/dd HH:mm:ss||yyyy/MM/dd||epoch_millis"
          },
          "date2": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "german_date1": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "german_date2": {
            "type": "date",
            "format": "dd.MM.yyyy"
          },
          "german_timestamp1": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "german_timestamp2": {
            "type": "date",
            "format": "dd.MM.yyyy HH:mm:ss"
          },
          "timestamp1": {
            "type": "date",
            "format": "yyyy/MM/dd HH:mm:ss||yyyy/MM/dd||epoch_millis"
          },
          "timestamp2": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          }
        }
      }
    }
  }
}

[jpountz]

However, if I add a particular Format to the mapping, the default date detection fails to recognize a date such as "2015/09/02"

This is the expected behaviour. You overrided the value of dynamic_date_formats so the old values are ignored. You would need to add strict_date_optional_time to the list of date formats.

[jpountz]

Fixed via #28375

[david-sitsky]

Prior to 6 dynamic date detection relies on a set of hardcoded characters so only strings that contain :, - or / are tested against the provided date formats.
We should probably document this behavior for versions prior to 6.

Please update the documentation! I am stuck on ES 5.5 and I wasted a huge amount of time until I found this issue.