ElasticSearch Query Range with timezone is not working

Elastic Stack Elasticsearch
1

Hi, I am currently facing a weird issue, when I am include timezone, the elastic search is returning zero hits:

{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 0,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  }
}

Whilst if i remove the timezone, it works:

{
  "took" : 2,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 3,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "business_logstash_wbsp",
        "_type" : "_doc",
        "_id" : "OEg573QBzOy_U32-2Hq1",
        "_score" : 1.0,
        "_source" : {
          "wbsp" : "smit           12345               1005000000000002"
        }
      },
      {
        "_index" : "business_logstash_wbsp",
        "_type" : "_doc",
        "_id" : "E0g573QBzOy_U32-GHor",
        "_score" : 1.0,
        "_source" : {
          "wbsp" : "smit           12345               1005000000000002"
        }
      },
      {
        "_index" : "business_logstash_wbsp",
        "_type" : "_doc",
        "_id" : "KUg573QBzOy_U32-iHoa",
        "_score" : 1.0,
        "_source" : {
          "wbsp" : "smit           12345               1005000000000002"
        }
      }
    ]
  }
}

Here is the sample query:

GET /business_logstash_wbsp/_search
{
    "_source": "wbsp",
   "query":{
      "range":{
         "@timestamp":{
            "gte":"2020-10-01T00:01:00",
            "lt":"2020-10-03T16:00:00"
         }
      }
   }
}

Ref: https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-range-query.html

2

Can you add a sample of the data that you think should be returned true along with the mapping?

3

Noted, I have made the changes to the question. Also note, the data returnined is the expected result. In the above sample, I have modified the date based to UTC (manually).

4

What type of field is wbsp and is this the data for it? (string)?

5

Yes, it is of type "string"

6

I believe timezone doesn't work on strings. Can't find the reference yet. Will update the post when I do. This would be due to it requiring to do math to calculate the date and you can't do that with a string.

This would mean you would need to convert to date or int if you wish to perform range queries using timezones.

7

Noted, However I am filitering on value of metadata @timestamp right, may i know how is "wbsp" field affecting the result.

8

I misread it. Apologies.

Can you post the query you are using to try the timezone?

9

No worries.

Ref: https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-range-query.html#range-query-time-zone

GET /business_logstash_wbsp/_search
{
   "_source": "wbsp",
   "query":{
        "range": {
          "@timestamp": {
            "time_zone": "+08:00",        
            "gte": "2020-10-01T00:01:00", 
            "lte": "2020-10-03T16:00:00"                  
          }
        }
      }
    }
1 Like
10

Everything looks correct. I am assuming you verified that after adjusting the timezone there are records that would be returned within that range?

11

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