Can one specify format of datetime in query?

Elastic Stack Elasticsearch
1

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.

2

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"
      }
    }
  }
}
3

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.

4

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：

5

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

6

: ). (timezones )

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

7

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