# I have wrote a query in elastic search, but it shows data ffrom 5:00 am only.Before that 12:00 AM to 04:00 AM data, it does not shows.Why it it like that only?it is problem due to time -zone or what?Kindly provide some suggestions.I am pasting query:

**URL:** <https://discuss.elastic.co/t/i-have-wrote-a-query-in-elastic-search-but-it-shows-data-ffrom-5-00-am-only-before-that-12-00-am-to-04-00-am-data-it-does-not-shows-why-it-it-like-that-only-it-is-problem-due-to-time-zone-or-what-kindly-provide-some-suggestions-i-am-pasting-query/109347>\
**Category:** Elasticsearch\
**Created:** [November 28, 2017, 9:18am UTC](https://discuss.elastic.co/t/i-have-wrote-a-query-in-elastic-search-but-it-shows-data-ffrom-5-00-am-only-before-that-12-00-am-to-04-00-am-data-it-does-not-shows-why-it-it-like-that-only-it-is-problem-due-to-time-zone-or-what-kindly-provide-some-suggestions-i-am-pasting-query/109347 "2017-11-28T09:18:28Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![vinayak](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vinayak/32/45342_2.png) [@vinayak](https://discuss.elastic.co/u/vinayak)\
**Post date:** [November 28, 2017, 9:18am UTC](https://discuss.elastic.co/t/i-have-wrote-a-query-in-elastic-search-but-it-shows-data-ffrom-5-00-am-only-before-that-12-00-am-to-04-00-am-data-it-does-not-shows-why-it-it-like-that-only-it-is-problem-due-to-time-zone-or-what-kindly-provide-some-suggestions-i-am-pasting-query/109347/1 "2017-11-28T09:18:28Z")

</div>

```
POST alertbrowser/_search?filter_path=aggregations.hourlyData.buckets
{
  "query":{
    "bool":{
      "must": [
        {
          "match": {
            "projectId": "aasish-webdemo-1-1498031057532"
          }
        }
      ],
      "filter": [
      {"range":{"page.time":{"gte":"now/d","lte":"now"}}}]
    }
  },
  "aggs": {
    "hourlyData": {
      "date_histogram": {
        "field": "page.time",
        "interval": "hour",
        "time_zone": "+05:30"
      } }
    }
  }

```

Above query output is:  
{  
"aggregations": {  
"hourlyData": {  
"buckets": [  
{  
"key\_as\_string": "2017-11-28T05:00:00.000+05:30",  
"key": 1511825400000,  
"doc\_count": 19  
},  
{  
"key\_as\_string": "2017-11-28T06:00:00.000+05:30",  
"key": 1511829000000,  
"doc\_count": 37  
},  
{  
"key\_as\_string": "2017-11-28T07:00:00.000+05:30",  
"key": 1511832600000,  
"doc\_count": 40  
},  
{  
"key\_as\_string": "2017-11-28T08:00:00.000+05:30",  
"key": 1511836200000,  
"doc\_count": 39  
},  
{  
"key\_as\_string": "2017-11-28T09:00:00.000+05:30",  
"key": 1511839800000,  
"doc\_count": 48  
},  
{  
"key\_as\_string": "2017-11-28T10:00:00.000+05:30",  
"key": 1511843400000,  
"doc\_count": 64  
},  
{  
"key\_as\_string": "2017-11-28T11:00:00.000+05:30",  
"key": 1511847000000,  
"doc\_count": 91  
},  
{  
"key\_as\_string": "2017-11-28T12:00:00.000+05:30",  
"key": 1511850600000,  
"doc\_count": 60  
},  
{  
"key\_as\_string": "2017-11-28T13:00:00.000+05:30",  
"key": 1511854200000,  
"doc\_count": 50  
},  
{  
"key\_as\_string": "2017-11-28T14:00:00.000+05:30",  
"key": 1511857800000,  
"doc\_count": 39  
}  
]  
}  
}  
}  
as in out put it is clearly showing that by adding time zone in query, it is showing result in from 05:00 Am,but data must be start from 12:00 AM, how to achieve this?If i am removing time zone then its shows data according to UTC?Please suggest................................

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [November 28, 2017, 9:41am UTC](https://discuss.elastic.co/t/i-have-wrote-a-query-in-elastic-search-but-it-shows-data-ffrom-5-00-am-only-before-that-12-00-am-to-04-00-am-data-it-does-not-shows-why-it-it-like-that-only-it-is-problem-due-to-time-zone-or-what-kindly-provide-some-suggestions-i-am-pasting-query/109347/2 "2017-11-28T09:41:56Z")

</div>

Please format your code using `</>` icon as explained in [this guide](https://discuss.elastic.co/t/about-the-elasticsearch-category/21). It will make your post more readable.

Or use markdown style like:

````
```
CODE
```

````

Elasticsearch always assumes UTC by default when you index a date (I mean **at index time** ).  
See [https://www.elastic.co/guide/en/elasticsearch/reference/6.0/date.html](https://www.elastic.co/guide/en/elasticsearch/reference/6.0/date.html)

If you index `2015-01-01T12:10:30`, it assumes `2015-01-01T12:10:30Z`.

You can change the way you are indexing your dates by providing the right TimeZone like `2015-01-01T12:10:30+05:30`

Or you can use `offset`: [https://www.elastic.co/guide/en/elasticsearch/reference/6.0/search-aggregations-bucket-datehistogram-aggregation.html#\_offset](https://www.elastic.co/guide/en/elasticsearch/reference/6.0/search-aggregations-bucket-datehistogram-aggregation.html#_offset)

HTH

---

<div class="post-metadata">

**Author:** ![vinayak](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vinayak/32/45342_2.png) [@vinayak](https://discuss.elastic.co/u/vinayak)\
**Post date:** [November 30, 2017, 11:59am UTC](https://discuss.elastic.co/t/i-have-wrote-a-query-in-elastic-search-but-it-shows-data-ffrom-5-00-am-only-before-that-12-00-am-to-04-00-am-data-it-does-not-shows-why-it-it-like-that-only-it-is-problem-due-to-time-zone-or-what-kindly-provide-some-suggestions-i-am-pasting-query/109347/3 "2017-11-30T11:59:13Z")

</div>

Thanks for suggestions.  
I have tried by adding time zone.After that also its giving result from 5:00 AM only.You can check it in output itself.How to get data from 12:00 am to upto 4:59 am??????

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [November 30, 2017, 12:17pm UTC](https://discuss.elastic.co/t/i-have-wrote-a-query-in-elastic-search-but-it-shows-data-ffrom-5-00-am-only-before-that-12-00-am-to-04-00-am-data-it-does-not-shows-why-it-it-like-that-only-it-is-problem-due-to-time-zone-or-what-kindly-provide-some-suggestions-i-am-pasting-query/109347/4 "2017-11-30T12:17:29Z")

</div>

> You can check it in output itself.

Where? How?

> How to get data from 12:00 am to upto 4:59 am??????

Did you try offset?

Would be easier if you could provide a full recreation script as described in

> [@About the Elasticsearch category](https://discuss.elastic.co/t/about-the-elasticsearch-category/21):
>
> The heart of the free and open Elastic Stack Elasticsearch is a distributed, RESTful search and analytics engine capable of addressing a growing number of use cases. As the heart of the Elastic Stack, it centrally stores your data for lightning fast search, fine‑tuned relevancy, and powerful analytics that scale with ease. warning PLEASE READ THIS SECTION IF IT'S YOUR FIRST POST Some useful links: [elasticsearch reference guide](http://www.elastic.co/guide/en/elasticsearch/reference/current/index.html)[elasticsearch user guide](http://www.elastic.co/guide/en/elasticsearch/guide/current/index.html)[elasticsearch plugins](https://www.elastic.co/guide/en/elasticsearch/plugins/current/index.html)[elasticsearch cl…](https://www.elastic.co/guide/en/elasticsearch/client/index.html)

It will help to better understand what you are doing.  
Please, try to keep the example as simple as possible.

---

<div class="post-metadata">

**Author:** ![vinayak](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vinayak/32/45342_2.png) [@vinayak](https://discuss.elastic.co/u/vinayak)\
**Post date:** [November 30, 2017, 12:48pm UTC](https://discuss.elastic.co/t/i-have-wrote-a-query-in-elastic-search-but-it-shows-data-ffrom-5-00-am-only-before-that-12-00-am-to-04-00-am-data-it-does-not-shows-why-it-it-like-that-only-it-is-problem-due-to-time-zone-or-what-kindly-provide-some-suggestions-i-am-pasting-query/109347/5 "2017-11-30T12:48:37Z")

</div>

```
POST alertbrowser/_search?filter_path=aggregations.hourlyData.buckets
{
  "query":{
    "bool":{
      "must": [
        {
          "match": {
            "projectId": "aasish-webdemo-1-1498031057532"
          }
        }
      ],
      "filter": [
      {"range":{"page.time":{"gte":"now/d","lte":"now"}}}]
    }
  },
  "aggs": {
    "hourlyData": {
      "date_histogram": {
        "field": "page.time",
        "interval": "hour",
        "time_zone": "+05:30"
      }         
    }
  }
}

```

Above query output is:

{  
"aggregations": {  
"hourlyData": {  
"buckets": [  
{  
**"key\_as\_string": "2017-11-30T05:00:00.000+05:30"** ,  
"key": 1511998200000,  
"doc\_count": 18  
},  
{  
"key\_as\_string": "2017-11-30T06:00:00.000+05:30",  
"key": 1512001800000,  
"doc\_count": 35  
},  
{  
"key\_as\_string": "2017-11-30T07:00:00.000+05:30",  
"key": 1512005400000,  
"doc\_count": 35  
},  
{  
"key\_as\_string": "2017-11-30T08:00:00.000+05:30",  
"key": 1512009000000,  
"doc\_count": 44  
},  
{  
"key\_as\_string": "2017-11-30T09:00:00.000+05:30",  
"key": 1512012600000,  
"doc\_count": 46  
},  
{  
"key\_as\_string": "2017-11-30T10:00:00.000+05:30",  
"key": 1512016200000,  
"doc\_count": 45  
},  
{  
"key\_as\_string": "2017-11-30T11:00:00.000+05:30",  
"key": 1512019800000,  
"doc\_count": 43  
},  
{  
"key\_as\_string": "2017-11-30T12:00:00.000+05:30",  
"key": 1512023400000,  
"doc\_count": 56  
},  
{  
"key\_as\_string": "2017-11-30T13:00:00.000+05:30",  
"key": 1512027000000,  
"doc\_count": 50  
},  
{  
"key\_as\_string": "2017-11-30T14:00:00.000+05:30",  
"key": 1512030600000,  
"doc\_count": 47  
},  
{  
"key\_as\_string": "2017-11-30T15:00:00.000+05:30",  
"key": 1512034200000,  
"doc\_count": 48  
},  
{  
"key\_as\_string": "2017-11-30T16:00:00.000+05:30",  
"key": 1512037800000,  
"doc\_count": 56  
},  
{  
"key\_as\_string": "2017-11-30T17:00:00.000+05:30",  
"key": 1512041400000,  
"doc\_count": 47  
},  
{  
"key\_as\_string": "2017-11-30T18:00:00.000+05:30",  
"key": 1512045000000,  
"doc\_count": 8  
}  
]  
}  
}  
}

In the above output it is started from 05:00 AM (I have marked that part in bold, for ease of identification).

I have not tried using offset.I am just trying and will tell you the output.Till then just check it above query and its output and let me know where i am committing mistake.

Thank you in advance.

---

<div class="post-metadata">

**Author:** ![vinayak](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vinayak/32/45342_2.png) [@vinayak](https://discuss.elastic.co/u/vinayak)\
**Post date:** [November 30, 2017, 1:13pm UTC](https://discuss.elastic.co/t/i-have-wrote-a-query-in-elastic-search-but-it-shows-data-ffrom-5-00-am-only-before-that-12-00-am-to-04-00-am-data-it-does-not-shows-why-it-it-like-that-only-it-is-problem-due-to-time-zone-or-what-kindly-provide-some-suggestions-i-am-pasting-query/109347/6 "2017-11-30T13:13:30Z")

</div>

Right now i am using elastic search version 5.5.2. In that no **offset** api is available.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [November 30, 2017, 3:50pm UTC](https://discuss.elastic.co/t/i-have-wrote-a-query-in-elastic-search-but-it-shows-data-ffrom-5-00-am-only-before-that-12-00-am-to-04-00-am-data-it-does-not-shows-why-it-it-like-that-only-it-is-problem-due-to-time-zone-or-what-kindly-provide-some-suggestions-i-am-pasting-query/109347/7 "2017-11-30T15:50:57Z")

</div>

Strange. It’s documented: [https://www.elastic.co/guide/en/elasticsearch/reference/5.5/search-aggregations-bucket-datehistogram-aggregation.html#\_offset](https://www.elastic.co/guide/en/elasticsearch/reference/5.5/search-aggregations-bucket-datehistogram-aggregation.html#_offset)

---

<div class="post-metadata">

**Author:** ![vinayak](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vinayak/32/45342_2.png) [@vinayak](https://discuss.elastic.co/u/vinayak)\
**Post date:** [December 11, 2017, 1:04pm UTC](https://discuss.elastic.co/t/i-have-wrote-a-query-in-elastic-search-but-it-shows-data-ffrom-5-00-am-only-before-that-12-00-am-to-04-00-am-data-it-does-not-shows-why-it-it-like-that-only-it-is-problem-due-to-time-zone-or-what-kindly-provide-some-suggestions-i-am-pasting-query/109347/8 "2017-12-11T13:04:48Z")

</div>

Yeah David you are [right.It](http://right.It) was my fault while writing in query. **offset api** is there.But offset is also not working.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [December 11, 2017, 1:54pm UTC](https://discuss.elastic.co/t/i-have-wrote-a-query-in-elastic-search-but-it-shows-data-ffrom-5-00-am-only-before-that-12-00-am-to-04-00-am-data-it-does-not-shows-why-it-it-like-that-only-it-is-problem-due-to-time-zone-or-what-kindly-provide-some-suggestions-i-am-pasting-query/109347/9 "2017-12-11T13:54:40Z")

</div>

What is the full recreation script and the result you got?

---

<div class="post-metadata">

**Author:** ![vinayak](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vinayak/32/45342_2.png) [@vinayak](https://discuss.elastic.co/u/vinayak)\
**Post date:** [December 12, 2017, 4:36am UTC](https://discuss.elastic.co/t/i-have-wrote-a-query-in-elastic-search-but-it-shows-data-ffrom-5-00-am-only-before-that-12-00-am-to-04-00-am-data-it-does-not-shows-why-it-it-like-that-only-it-is-problem-due-to-time-zone-or-what-kindly-provide-some-suggestions-i-am-pasting-query/109347/10 "2017-12-12T04:36:12Z")

</div>

My recreation script is like that :

```
  POST alertbrowser/_search?filter_path=aggregations.hourlyData.buckets
    {
      "query":{
        "bool":{
          "must": [
            {
              "match": {
                "projectId": "aasish-webdemo-1-1498031057532"
              }
            }
          ],
          "filter": [
          {"range":{"page.time":{"gte":"now/d","lte":"now"}}}]
        }
      },
      "aggs": {
        "hourlyData": {
          "date_histogram": {
            "field": "page.time",
            "interval": "day",
            "offset": "+330m"
          }         
        }
      }
    }

```

And resulted output is:

```
{
  "aggregations": {
    "hourlyData": {
      "buckets": [
        {
          "key_as_string": "2017-12-11T05:30:00.000Z",
          "key": 1512970200000,
          "doc_count": 2
        }
      ]
    }
  }
}

```

Again it is showing data from 05:30:00.000z on wards. As per my understanding **offset api** it will simply change the bucket using specified offset parameter [value.So](http://value.So) here in my case i mentioned offset parameter value as **+330 minute** so its just shift the bucket interval from midnight 12:00 Am to morning 05:330 AM.

---

<div class="post-metadata">

**Author:** ![vinayak](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vinayak/32/45342_2.png) [@vinayak](https://discuss.elastic.co/u/vinayak)\
**Post date:** [December 12, 2017, 5:00am UTC](https://discuss.elastic.co/t/i-have-wrote-a-query-in-elastic-search-but-it-shows-data-ffrom-5-00-am-only-before-that-12-00-am-to-04-00-am-data-it-does-not-shows-why-it-it-like-that-only-it-is-problem-due-to-time-zone-or-what-kindly-provide-some-suggestions-i-am-pasting-query/109347/11 "2017-12-12T05:00:35Z")

</div>

Without mentioning either **time-zone** or **offset** query simply returns results from 00:00:00.000z on wards. Query is as follows:

```
POST alertbrowser/_search?filter_path=aggregations.hourlyData.buckets
{
  "query":{
    "bool":{
      "must": [
        {
          "match": {
            "projectId": "aasish-webdemo-1-1498031057532"
          }
        }
      ],
      "filter": [
      {"range":{"page.time":{"gte":"now/d","lte":"now"}}}]
    }
  },
  "aggs": {
    "hourlyData": {
      "date_histogram": {
        "field": "page.time",
        "interval": "hour"
      }         
    }
  }
} 

```

From this query we are getting result as:

```
{
  "aggregations": {
    "hourlyData": {
      "buckets": [
        {
          "key_as_string": "2017-12-12T02:00:00.000Z",
          "key": 1513044000000,
          "doc_count": 2
        },
        {
          "key_as_string": "2017-12-12T03:00:00.000Z",
          "key": 1513047600000,
          "doc_count": 0
        },
        {
          "key_as_string":"2017-12-12T04:00:00.000Z",
         "key": 1513051200000,
          "doc_count": 6
        }
      ]
    }
  }
}

```

So in output you can check that the last data point we are getting having @time as "2017-12-12T04:00:00.000Z", while in our time zone it is 09:30:00.000z AM.You can also check it with the help of epoch time mentioned in result as key i.e. "key": 1513051200000.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [December 12, 2017, 8:22am UTC](https://discuss.elastic.co/t/i-have-wrote-a-query-in-elastic-search-but-it-shows-data-ffrom-5-00-am-only-before-that-12-00-am-to-04-00-am-data-it-does-not-shows-why-it-it-like-that-only-it-is-problem-due-to-time-zone-or-what-kindly-provide-some-suggestions-i-am-pasting-query/109347/12 "2017-12-12T08:22:58Z")

</div>

I can't reproduce as I don't have a full script to reproduce.

That said I'm going to tell you what I told you at the very first:

> You can change the way you are indexing your dates by providing the right TimeZone like `2015-01-01T12:10:30+05:30`

You should definitely provide correct dates to begin with. Trying to find workaround might be hard.  
If you still want to go that way, please provide a full recreation script we can play with.

---

<div class="post-metadata">

**Author:** ![vinayak](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vinayak/32/45342_2.png) [@vinayak](https://discuss.elastic.co/u/vinayak)\
**Post date:** [December 15, 2017, 5:55am UTC](https://discuss.elastic.co/t/i-have-wrote-a-query-in-elastic-search-but-it-shows-data-ffrom-5-00-am-only-before-that-12-00-am-to-04-00-am-data-it-does-not-shows-why-it-it-like-that-only-it-is-problem-due-to-time-zone-or-what-kindly-provide-some-suggestions-i-am-pasting-query/109347/13 "2017-12-15T05:55:16Z")

</div>

Thanx for your support David Pilato. We reached on a conclusion that it is due to the time [zone.So](http://zone.So) for the time being we will be oky with the same result.  
Thnx agai.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [January 12, 2018, 5:55am UTC](https://discuss.elastic.co/t/i-have-wrote-a-query-in-elastic-search-but-it-shows-data-ffrom-5-00-am-only-before-that-12-00-am-to-04-00-am-data-it-does-not-shows-why-it-it-like-that-only-it-is-problem-due-to-time-zone-or-what-kindly-provide-some-suggestions-i-am-pasting-query/109347/14 "2018-01-12T05:55:31Z")

</div>

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