Can one specify format of datetime in query?

Is there a way of specifying the format of times used in a query? In most of my indexes I convert times to ISO8601 but one data source had a totally non standard format and I decided that it was easier to simply specify a custom format in the mapping.

Now I am trying to extract data using the API and I find that I have to use the same data format as the _source.

Once you successfully insert data as date type,you can query in any data format

PUT data
{
  "mappings": {
    "properties": {
      "date": {
        "type": "date" 
      }
    }
  }
}
PUT data/_doc/1
{ "date": "2015-01-01" } 

PUT data/_doc/2
{ "date": "2015-01-01T12:10:30Z" } 

PUT data/_doc/3
{ "date": 1420070400001 }

POST data/_search
{
  "query": {
    "range": {
      "date": {
        "gte": "2014-01-01T12:10:30Z",
        "lte": "1520070400001"
      }
    }
  }
}

Thanks Caster, that's what I expected!

So why do I get:

 query >>>> index dns_ds {:size=>10000, :sort=>[{:@timestamp=>{:order=>"asc"}}], :query=>{:bool=>{:must=>[{:range=>{:@timestamp=>{:gte=>"02-Mar-2022 00:00:00 +1300", :lte=>"04-Mar-2022 00:00:00 +1300"}}}]}}}

in rescue [400] {"error":{"root_cause":[{"type":"parse_exception","reason":"failed to parse date field [02-Mar-2022 00:00:00 +1300] with format [dd-MMM-yyyy HH:mm:ss.SSS Z]:

mapping for @timestamp:

    "@timestamp": {
      "format": "dd-MMM-yyyy HH:mm:ss.SSS Z",
      "index": true,
      "ignore_malformed": false,
      "store": false,
      "type": "date",
      "doc_values": true
    },

not the missing millisecs.

I get parse errors if I use anything other than the exact format in the mapping.

you should customize your own date format,as below

PUT data2
{
  "mappings": {
    "properties": {
      "date": {
        "type":   "date",
        "format": "dd-MMM-yyyy HH:mm:ss Z||strict_date_optional_time||epoch_millis"
      }
    }
  }
}
POST data2/_doc
{
  "date": "02-Mar-2022 00:00:00 +8"
}
POST data2/_doc
{
  "date": "2015-01-01T12:10:30Z"
}
POST data2/_search

docs is here:

not 02-Mar-2022 00:00:00 +8
try your 04-Mar-2022 00:00:00 +1300

: ). (timezones )

and thanks! So if you specify format in mapping you need to include the mappings that you want to search with too.

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