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

**URL:** <https://discuss.elastic.co/t/mysql-yyyy-mm-dd-hh-mm-ss-datetime-into-elasticsearch/78775>\
**Category:** Elasticsearch\
**Created:** [March 15, 2017, 10:42pm UTC](https://discuss.elastic.co/t/mysql-yyyy-mm-dd-hh-mm-ss-datetime-into-elasticsearch/78775 "2017-03-15T22:42:16Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![adrienpp](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@adrienpp](https://discuss.elastic.co/u/adrienpp)\
**Post date:** [March 15, 2017, 10:42pm UTC](https://discuss.elastic.co/t/mysql-yyyy-mm-dd-hh-mm-ss-datetime-into-elasticsearch/78775/1 "2017-03-15T22:42:16Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![cbuescher](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cbuescher/32/60402_2.png) [@cbuescher](https://discuss.elastic.co/u/cbuescher)\
**Post date:** [March 16, 2017, 3:00pm UTC](https://discuss.elastic.co/t/mysql-yyyy-mm-dd-hh-mm-ss-datetime-into-elasticsearch/78775/2 "2017-03-16T15:00:18Z")

</div>

Hi,

this works for me:

```auto
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:

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

```

---\>

```auto
"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](https://github.com/elastic/elasticsearch/issues/10005)

---

<div class="post-metadata">

**Author:** ![adrienpp](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@adrienpp](https://discuss.elastic.co/u/adrienpp)\
**Post date:** [March 16, 2017, 3:21pm UTC](https://discuss.elastic.co/t/mysql-yyyy-mm-dd-hh-mm-ss-datetime-into-elasticsearch/78775/3 "2017-03-16T15:21:02Z")

</div>

Thank you!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [April 13, 2017, 3:21pm UTC](https://discuss.elastic.co/t/mysql-yyyy-mm-dd-hh-mm-ss-datetime-into-elasticsearch/78775/4 "2017-04-13T15:21:17Z")

</div>

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