Timezone-affected date histogram


#1

Hello!

I have a following query coming from PHP:

{
  "size": 0,
  "aggs": {
    "statuses_histogram": {
      "date_histogram": {
        "field": "created_at",
        "interval": "1d",
        "min_doc_count": 0,
        "extended_bounds": {
          "min": "2018-10-01T00:00:00+02:00",
          "max": "2018-10-09T23:59:59+02:00"
        }
      },
      "aggs": {
        "statuses_histogram": {
          "terms": {
            "field": "status",
            "order": {
              "_key": "asc"
            }
          }
        }
      }
    }
  }
}

This should return a date aggregation of how many times a certain status repeated per day. It should have 7 days as a result, however, the result I'm getting contains 31 day and it goes from 2018-09-30T00:00:00.000Z to 2018-10-30T00:00:00.000Z. How is this possible? What am I doing wrong?

It is important that the days in the aggregation are matched to the user's timezone, not UTC. I've got users from Australia and having date aggregations by UTC timezone makes no sense for them.


(Ignacio Vera) #2

extended_bounds does not filter the data in your index, it only allows to extend the bounds of your aggregation if needed. You need to add a range query to filter the data you are aggregating.


#3

Thank you!

All questions from the original post resolved with the following query:

{
  "size": 0,
  "aggs": {
    "statuses_histogram": {
      "date_histogram": {
        "field": "created_at",
        "interval": "1d",
        "min_doc_count": 0,
        "time_zone": "Europe/Zagreb",
        "extended_bounds": {
          "min": "2018-10-01T00:00:00+02:00",
          "max": "2018-10-09T23:59:59+02:00"
        }
      },
      "aggs": {
        "statuses_histogram": {
          "terms": {
            "field": "status",
            "order": {
              "_key": "asc"
            }
          }
        }
      }
    }
  },
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "created_at": {
              "gte": "2018-10-01T00:00:00+02:00",
              "lte": "2018-10-09T23:59:59+02:00"
            }
          }
        }
      ]
    }
  }
}

(using bool query because other parameters not relevant to this topic are being used as well)


(system) #4

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