# Not able to index dates from mariaDb and to do range queries

**URL:** <https://discuss.elastic.co/t/not-able-to-index-dates-from-mariadb-and-to-do-range-queries/57320>\
**Category:** Elasticsearch\
**Created:** [August 5, 2016, 8:52am UTC](https://discuss.elastic.co/t/not-able-to-index-dates-from-mariadb-and-to-do-range-queries/57320 "2016-08-05T08:52:21Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![anacicconi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/anacicconi/32/11222_2.png) [@anacicconi](https://discuss.elastic.co/u/anacicconi)\
**Post date:** [August 5, 2016, 8:52am UTC](https://discuss.elastic.co/t/not-able-to-index-dates-from-mariadb-and-to-do-range-queries/57320/1 "2016-08-05T08:52:21Z")

</div>

Hello,

I'm trying to index a date field to Elastic and I'm not able to define the format in my mapping. I tried almost all of the formats defined in this page that could match the way my data is stored: [https://www.elastic.co/guide/en/elasticsearch/reference/1.4/mapping-date-format.html](https://www.elastic.co/guide/en/elasticsearch/reference/1.4/mapping-date-format.html)

This is the kind of bug I have:

```
index: ...2447 caused MapperParsingException[failed to parse [publishAt]]; nested: MapperParsingException[failed to parse date field [2016-08-03T00:00:0  
  0+00:00], tried both date format [dd-MM-yyyy HH:mm:ss||yyyy-MM-dd'T'HH:mm:ss.SSSZZ], and timestamp number with locale []]; nested: IllegalArgumentException[Invalid format: "  
  2016-08-03T00:00:00+00:00" is malformed at "+00:00"];   

```

I'm using MariaDb with a regular datetime format. I store my dates using DateTime PHP objects so there's nothing really unconventional.

If I don't define any format, I'm able to index but then I'm not able to use range query. I have strange results. Sometimes, I have zero results and sometimes I have results that are not related to the year I requested. I think this must be related to the mapping problem.

Here's my mapping:

```
mappings:
       publishAt:
              type: date
              format: dd-MM-yyyy HH:mm:ss||yyyy-MM-dd'T'HH:mm:ss.SSSZZ

```

And here's my query in a simplified version:

```
{
   "query": {
    "range": {
      "publishAt": {
        "gte": "2016",
        "lte": "2016",
        "format": "yyyy"
      }
    }
  },
  "aggs": {
    "content": {
      "terms": {
        "field": "_type"
      }
    }
  }
}

```

Does anybody know what I'm doing wrong? I just want to search the contents published on the year 2016. Maybe the range query is not the best solution neither. If you have other ideas, please let me know. I'm using the version 1.7 of Elastic Search.

Thank you!

---

<div class="post-metadata">

**Author:** ![colings86](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/colings86/32/44960_2.png) [@colings86](https://discuss.elastic.co/u/colings86)\
**Post date:** [August 5, 2016, 9:00am UTC](https://discuss.elastic.co/t/not-able-to-index-dates-from-mariadb-and-to-do-range-queries/57320/2 "2016-08-05T09:00:39Z")

</div>

The mapping you have defines the format as `dd-MM-yyyy HH:mm:ss||yyyy-MM-dd'T'HH:mm:ss.SSSZZ` but your date is `2016-08-03T00:00:00+00:00`. Neither of your formats match; the first is completely different and the second expects the same format but with milliseconds so would match `2016-08-03T00:00:00.000+00:00`. You probably need to either change the second format to be `yyyy-MM-dd'T'HH:mm:ssZZ` or add that format as a third one in the list.

Hope this helps

---

<div class="post-metadata">

**Author:** ![anacicconi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/anacicconi/32/11222_2.png) [@anacicconi](https://discuss.elastic.co/u/anacicconi)\
**Post date:** [August 5, 2016, 9:07am UTC](https://discuss.elastic.co/t/not-able-to-index-dates-from-mariadb-and-to-do-range-queries/57320/3 "2016-08-05T09:07:09Z")

</div>

> [@colings86](#):
>
> yyyy-MM-dd'T'HH:mm:ssZZ

Thank you so much! You saved my life! I've been trying so many formats since yesterday. I must have miswritten this format because I remember trying it. About the range query, is this the right way to find contents at a certain year? I find it a little bit strange to query on a range if I want a specific date.

By the way, this still returns null even if most of my contents were created on 2016. If I add format: yyyy it doesn't work neither. When I read the doc, I understood I could define the format to query only on the year: [Range query | Elasticsearch Guide [8.11] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-range-query.html#_date_format_in_range_queries)

```
{
  "query": {
    "range": {
      "publishAt": {
        "gte": "2016",
        "lte": "2016"
      }
    }
  },
  "aggs": {
    "content_type": {
      "terms": {
        "field": "_type"
      }
    }
  }
}

```

---

<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:** [July 5, 2017, 10:29pm UTC](https://discuss.elastic.co/t/not-able-to-index-dates-from-mariadb-and-to-do-range-queries/57320/4 "2017-07-05T22:29:51Z")

</div>