MySQL yyyy-MM-dd HH:mm:ss datetime into ElasticSearch

[adrienpp]

Hello,

I am trying to insert a date such as 2017-03-15 23:39:03 into ElasticSearch.

My mapping is:

"inserted": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss
}

But I'm getting the following error:

org.elasticsearch.index.mapper.MapperParsingException: failed to parse [inserted]

etc...

failed to execute bulk item (index) index {[elastiquent][siteContenu][4495], source[{"inserted":{"date":"2017-03-15 23:39:03.000000","timezone_type":3,"timezone":"UTC"},"updated":{"date":"2017-03-15 23:39:03.000000","timezone_type":3,"timezone":"UTC" etc

Apparently six zeros are being added, but id doesn't work neither with yyyy-MM-dd HH:mm:ss.SSS or yyyy-MM-dd HH:mm:ss.SSSSSS as the date format.

Please help!

Thank you.

[cbuescher (Christoph)]

Hi,

this works for me:

PUT test
{
  "mappings": {
    "type": {
      "properties": {
        "inserted": {
          "type": "date",
          "format": "yyyy-MM-dd HH:mm:ss.SSSSSS"
        }
      }
    }
  }
}

PUT /test/type/1
{
  "inserted" : "2017-03-15 23:39:03.000000"
}

PUT /test/type/2
{
  "inserted" : "2017-03-15 23:39:03.002000"
}

However, be aware that Elasticsearchs date type internally stores dates with only milliseconds precision as you can see when you search for the above documents and look at the internally stored values:

GET /test/type/_search
{
  "docvalue_fields" : ["inserted"]
}

--->

"hits": {
    "total": 2,
    "max_score": 1,
    "hits": [
      {
        "_index": "test",
        "_type": "type",
        "_id": "2",
        "_score": 1,
        "_source": {
          "inserted": "2017-03-15 23:39:03.002000"
        },
        "fields": {
          "inserted": [
            1489621143002
          ]
        }
      },
      {
        "_index": "test",
        "_type": "type",
        "_id": "1",
        "_score": 1,
        "_source": {
          "inserted": "2017-03-15 23:39:03.000000"
        },
        "fields": {
          "inserted": [
            1489621143000
          ]
        }
      }
    ]
  }

This is a known issue: https://github.com/elastic/elasticsearch/issues/10005

[adrienpp]

Thank you!