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:

Elastic Stack Elasticsearch
1 
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................................

2

Please format your code using </> icon as explained in this guide. 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

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

HTH

1 Like
3

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??????

4

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

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

5 
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.

6

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

7

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

8

Yeah David you are right.It was my fault while writing in query. offset api is there.But offset is also not working.

9

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

10

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 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.

11

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.

12

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.

13

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

14

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