Fetch results based on Timezone

[anudeep_k]

Hi,

I have a inserted a data in elastic search from my timezone(Asia/Kolkata; +05:30) and converted into UTC.please find the below table for reference.

Now I am trying to fetch the data by passing my timezone for which i have to view the inserted data in local time(09:30). But the data fetched is still in UTC even when the local timezone is passed.Can some one help me out with this?

[spinscale (Alexander Reelsen)]

just to fully understand this. you have inserted a document with a date like this

{
  "timestamp" : "2019-11-08T12:34:56.789+5:30"
}

And now you would like to query it as such. Let's take this example

DELETE test

PUT test/_doc/1?refresh
{
  "timestamp" : "2019-11-08T01:00:00.000+05:30"
}

GET test/_mapping/field/timestamp

# no match, in UTC
GET test/_search
{
  "query": {
    "range": {
      "timestamp": {
        "gte": "2019-11-08"
      }
    }
  }
}

# match, in UTC
GET test/_search
{
  "query": {
    "range": {
      "timestamp": {
        "gte": "2019-11-07"
      }
    }
  }
}

# match in local timezone
GET test/_search
{
  "query": {
    "range": {
      "timestamp": {
        "gte": "2019-11-08T01:00:00.000+05:30"
      }
    }
  }
}

[anudeep_k]

Thanks for the time and effort you have put. Say I have inserted a record in UTC,

"timestamp" : "2019-11-10 08:00:00"
"name" : "xxx"

My time zone is Asia/Kolkata (i.e +05:30). Now if I wanted to fetch this record as below .

"timestamp" : "2019-11-10 13:30:00"
"name" : "xxx"

Even if the time zone is passed in the query I am still getting the record in UTC

"timestamp" : "2019-11-10 08:00:00"
"name" : "xxx"

[spinscale (Alexander Reelsen)]

please share a fully reproducible example

[anudeep_k]

I have inserted a data at given time in milliseconds. I converted my local date time to UTC in milliseconds.

POST /test/data/sample
{
"start_time": "1573453784307"
}

Now am trying to fetch the data by using the below query

GET /test/data/_search
{ 
"size":0,
"query":{ 
    "bool":{ 
        "filter":[ 
            { 
                "range":{ 
                    "start_time":{ 
                        "gte":"2019-11-11 00:00:00.000000+0530"
                    }
                }
            }
        ]
    }
},
"aggregations":{ 
    "timeslice":{ 
        "date_histogram":{ 
            "field":"start_time",
            "format":"yyyy-MM-dd HH:mm:ss",
             "time_zone": "+05:30", 
            "calendar_interval":"hour",
            "extended_bounds":{
                "min":"2019-11-11 00:00:00",
                "max":"2019-11-11 23:59:59"
            },
            "order":{ 
                "_key":"desc"
            }
         }
      }
   }
}

I am expecting the doc_count in the time range as below, since my timezone is +0530

{
      "key_as_string" : "2019-11-11 17:00:00",
      "key" : 1573471800000,
      "doc_count" : 1
}

But the results I have been getting is (still in UTC range)

{
      "key_as_string" : "2019-11-11 11:00:00",
      "key" : 1573450200000,
      "doc_count" : 1
}

Can you please help me out with this?