Timezone adjustments in query


(Darshan Parab) #1

I wrote a query to fetch data from and index and aggregate it using date_histogram aggregation. I'm working in IST (GMT+5.30) Timezone. I applied a filter to get events occurred today using "now/d" expression. Apparantly, this results in 0000 Hrs in GMT. As a result, I'm getting incomplete results. Looking out for the way so that "now/d" returns 0000 Hrs in IST Timezone.

When I Visualize the data using Kibana, it picks up IST timezone and gives correct results. Only when I query elasticsearch using dev tools or python, I notice the said behavior.

Find below the query and corresponding results.

{
"size": 0,
"query": {
"bool": {
"filter": {
"range": {
"timestamp": {
"gte": "now/d"
}
}
}
}
},
"aggs": {
"timestamp": {
"date_histogram": {
"field": "timestamp",
"interval": "hour"
}
}
}
}

{
"took" : 86,
"timed_out" : false,
"_shards" : {
"total" : 5,
"successful" : 5,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : 1332097,
"max_score" : 0.0,
"hits" :
},
"aggregations" : {
"timestamp" : {
"buckets" : [
{
"key_as_string" : "2019-02-15T00:00:00.000Z",
"key" : 1550188800000,
"doc_count" : 16063
},
{
"key_as_string" : "2019-02-15T01:00:00.000Z",
"key" : 1550192400000,
"doc_count" : 16818
},
{
"key_as_string" : "2019-02-15T02:00:00.000Z",
"key" : 1550196000000,
"doc_count" : 19957
},
{
"key_as_string" : "2019-02-15T03:00:00.000Z",
"key" : 1550199600000,
"doc_count" : 34773
},
{
"key_as_string" : "2019-02-15T04:00:00.000Z",
"key" : 1550203200000,
"doc_count" : 80339
},
{
"key_as_string" : "2019-02-15T05:00:00.000Z",
"key" : 1550206800000,
"doc_count" : 130403
},
{
"key_as_string" : "2019-02-15T06:00:00.000Z",
"key" : 1550210400000,
"doc_count" : 165722
},
{
"key_as_string" : "2019-02-15T07:00:00.000Z",
"key" : 1550214000000,
"doc_count" : 154612
},
{
"key_as_string" : "2019-02-15T08:00:00.000Z",
"key" : 1550217600000,
"doc_count" : 143965
},
{
"key_as_string" : "2019-02-15T09:00:00.000Z",
"key" : 1550221200000,
"doc_count" : 158271
},
{
"key_as_string" : "2019-02-15T10:00:00.000Z",
"key" : 1550224800000,
"doc_count" : 176818
},
{
"key_as_string" : "2019-02-15T11:00:00.000Z",
"key" : 1550228400000,
"doc_count" : 132230
},
{
"key_as_string" : "2019-02-15T12:00:00.000Z",
"key" : 1550232000000,
"doc_count" : 102126
}
]
}
}
}

Query with timezone adjustment and corresponding results.

{
"size": 0,
"query": {
"bool": {
"filter": {
"range": {
"timestamp": {
"gte": "now/d-330m"
}
}
}
}
},
"aggs": {
"timestamp": {
"date_histogram": {
"field": "timestamp",
"interval": "hour"
}
}
}
}

{
"took" : 276,
"timed_out" : false,
"_shards" : {
"total" : 5,
"successful" : 5,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : 1426095,
"max_score" : 0.0,
"hits" :
},
"aggregations" : {
"timestamp" : {
"buckets" : [
{
"key_as_string" : "2019-02-14T18:00:00.000Z",
"key" : 1550167200000,
"doc_count" : 11035
},
{
"key_as_string" : "2019-02-14T19:00:00.000Z",
"key" : 1550170800000,
"doc_count" : 20879
},
{
"key_as_string" : "2019-02-14T20:00:00.000Z",
"key" : 1550174400000,
"doc_count" : 17171
},
{
"key_as_string" : "2019-02-14T21:00:00.000Z",
"key" : 1550178000000,
"doc_count" : 16916
},
{
"key_as_string" : "2019-02-14T22:00:00.000Z",
"key" : 1550181600000,
"doc_count" : 13300
},
{
"key_as_string" : "2019-02-14T23:00:00.000Z",
"key" : 1550185200000,
"doc_count" : 14697
},
{
"key_as_string" : "2019-02-15T00:00:00.000Z",
"key" : 1550188800000,
"doc_count" : 16063
},
{
"key_as_string" : "2019-02-15T01:00:00.000Z",
"key" : 1550192400000,
"doc_count" : 16818
},
{
"key_as_string" : "2019-02-15T02:00:00.000Z",
"key" : 1550196000000,
"doc_count" : 19957
},
{
"key_as_string" : "2019-02-15T03:00:00.000Z",
"key" : 1550199600000,
"doc_count" : 34773
},
{
"key_as_string" : "2019-02-15T04:00:00.000Z",
"key" : 1550203200000,
"doc_count" : 80339
},
{
"key_as_string" : "2019-02-15T05:00:00.000Z",
"key" : 1550206800000,
"doc_count" : 130403
},
{
"key_as_string" : "2019-02-15T06:00:00.000Z",
"key" : 1550210400000,
"doc_count" : 165722
},
{
"key_as_string" : "2019-02-15T07:00:00.000Z",
"key" : 1550214000000,
"doc_count" : 154612
},
{
"key_as_string" : "2019-02-15T08:00:00.000Z",
"key" : 1550217600000,
"doc_count" : 143965
},
{
"key_as_string" : "2019-02-15T09:00:00.000Z",
"key" : 1550221200000,
"doc_count" : 158271
},
{
"key_as_string" : "2019-02-15T10:00:00.000Z",
"key" : 1550224800000,
"doc_count" : 176818
},
{
"key_as_string" : "2019-02-15T11:00:00.000Z",
"key" : 1550228400000,
"doc_count" : 132230
},
{
"key_as_string" : "2019-02-15T12:00:00.000Z",
"key" : 1550232000000,
"doc_count" : 102126
}
]
}
}
}

Suggestions if anyone have faced similar issues.
Thanks


(system) closed #2

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