Timezone in range query builder for epoch timestamp

[jpmiitian (Jay Prakash Meena)]

Hi

I have timestamp field in the ES Doc which has epoch timestamp UTC [long value].
Ques :

1. How can I retrieve data in other time zone from the epoch timestamps in ES.

2. As we have support in DateHistogramAggregationBuilder for time zone,
   Do we have similar support for time range query builder ?

    // date Histogram code snippet 
    String zoneId = "Asia/Tokyo" ;
    DateTimeZone dateTimeZone =                   
    DateTimeZone.forTimeZone(TimeZone.getTimeZone(ZoneId.of(zoneId))) ;
    dateHistogramAggregationBuilder.timeZone( dateTimeZone);
   
    // Range Query Builder
    QueryBuilder rangeQueryBuilder =  QueryBuilders.rangeQuery(timeFieldInDoc).gte(startTime).lt(endTime).timeZone(zoneId) ; // **exception** 
   
    caused_by":{"type":"illegal_argument_exception","reason":"time_zone must be UTC for format 
    [epoch_millis]"}
   
   
   
   GET /myindex/type/_search
    {
      "query": {
        "bool": {
         "must": [
           {"match": {
             "randomId": "60PEJO"
           } }
         ], 
         "filter": {
           "range": {
             "timestamp": {
               "gte": 10,
               "lte": 207847237915672365,
                "time_zone": "+01:00"
             }
           }
         } 
        }
      }
    }

[jpmiitian (Jay Prakash Meena)]

@cbuescher Any idea about this OR Is there way to retrieve dayOfWeek from timestamp[which is in UTC] to some other timezone .

Thanks

[cbuescher (Christoph)]

Hi @jpmiitian,

finally found some time to try this, sorry for the delay in getting back. Timezones don't work with "epoch_millis" format in range, but you can specify another format in the range query, even if the field you query has "epoch_millis" format. I just tried the following in ES 6.1.1:

GET /my_index/_mapping

{
  "my_index": {
    "mappings": {
      "my_type": {
        "properties": {
          "date": {
            "type": "date",
            "format": "epoch_millis"
          }
        }
      }
    }
  }
}

PUT /my_index/my_type/1
{
  "date" : 1514761200000  <-- Thats 2018-01-01 00:00:00 in UTC+1
}

GET /my_index/_search
{
    "query": {
        "range" : {
            "date" : {
                "gte": "2018-01-01", 
                "lte": "2018-01-02",
                "format": "yyyy-MM-dd", 
                "time_zone": "+01:00"
            }
        }
    }
}

"hits": {
    "total": 1,
    "max_score": 1,
    "hits": [
      {
        "_index": "my_index",
        "_type": "my_type",
        "_id": "1",
        "_score": 1,
        "_source": {
          "date": 1514761200000
        }
      }
    ]
  }

With timezone "-01:00" there are no hits, so the zone takes effect.
I only tried the rest endpoint but this should also apply to the JAVA Api.

@jpmiitian

Is there way to retrieve dayOfWeek from timestamp

I think you might be able to use scripts for doing this in aggregations, but this depends very much on the use case I think. I'd open a separate issue for this since I'm not too familiar with this myself.