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

Elastic Stack Elasticsearch
1

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

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!

2

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

3

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

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