Range condition doesn't work for minutes

Elastic Stack Elasticsearch
1

Hello,

I'm having an issue with the range filter, here is the context :

I'm trying to do a simple query that filters on datetime field.

My document have a simple mapping :

"TRAITEMENT": {
    "properties": {
      "dat_cre_tra": {
        "type": "date",
        "format": "dd/MM/yyyy HH:mm:ss",
        "locale": "fr"
      },
      "typ_tra": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
}

A sample document is inserted as this one :

{
	  "num_tra": "FLU0001_tra_0001",
	  "TRAITEMENT" : {
	  "dat_cre_tra": "08/10/2019 11:30:00",
	  "sta_tra": "E",
	  "ind_acq_tra": "N",
	  "typ_err": "4",
	  "typ_tra" : "RGP"
	  }

I'm trying to retrieve the "Traitements" that have a dat_cre_tra that littler than 15 minutes like this

{
 "query" :{
   "bool": {
    "must": [
  {
    "range": {
      "TRAITEMENT.dat_cre_tra": {
        "gt" : "now-15y",
        "lt" : "now+15m",
        "time_zone": "+01:00"
      }
    }
  }
]
}
}
}

but I didn't make it. So I started investigating and i started to query with different time units like this one :

{
  "query" :{
  "bool": {
    "must": [
      {
        "range": {
          "TRAITEMENT.dat_cre_tra": {
            "gt" : "now-15y",
            "lt" : "now+2h",
            "time_zone": "+01:00"
          }
        }
      }
    ]
  }
}
}

This one works, the document is returned, whereas the same query with lt : now+1h does not work.

To resume, the document is only returned when the lt filter is bigger than 1h.

Am I missing something ?

Thanks for help :slight_smile:

2

just a quick reminder, a reproducible example would help a lot here (as the dates are static, this is pretty hard here I know :slight_smile:

Just to make sure you are aware of that. Unless you specify a timezone, the timezone is assumed to be UTC when indexing, which potentially adds on hour offset. Specifying a locale does not change that behaviour.

3

Hello,

Thanks for answer,

This is helpfull

So, what's my next step, do I add the timezone in the mapping (not sure it's possible) Or i specify timezone in my query (didn't change anything) or specify timezone on document indexing (how to do this?) ?

Thank you

4

the most important thing is to specify the timezone when indexing (as you cannot specify a default timezone in the mapping). This is also the only way to know what timezone the event was in when being stored.

You need to change your mapping date format to also include the timezone and then adapt your ingestion tooling to also add this.

hope this help.

5

Thanks for you help,

So there is no default timezone for the mapping, then my chance is to add the timezone to each datetime field. I tried to do this that way :

 "dat_cre_tra": {
        "type": "date",
        "format": "dd/MM/yyyy HH:mm:ss",
        "timezone" : "Europe/Paris",
        "locale": "fr"
      },

But vain. I'm sorry I can't find the doc specifications for it.

Can you help ?
Thanks a alot

6

For this mapping, i get the error :

 "root_cause": [
  {
    "type": "mapper_parsing_exception",
    "reason": "Mapping definition for [dat_cre_tra] has unsupported parameters:  [timezone : Europe/Paris]"
  }
],
"type": "mapper_parsing_exception",
"reason": "Mapping definition for [dat_cre_tra] has unsupported parameters:  [timezone : Europe/Paris]"

},

7

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