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:


(vinayak) #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................................


(David Pilato) #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


(vinayak) #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??????


(David Pilato) #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.


(vinayak) #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.


(vinayak) #6

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


(David Pilato) #7

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


(vinayak) #8

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


(David Pilato) #9

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


(vinayak) #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.


(vinayak) #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.


(David Pilato) #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.


(vinayak) #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.


(system) #14

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