Dynamic time zone offset in elasticsearch aggregation?

Question

I'm aggregating documents that each have a timestamp. The timestamp is UTC, but the documents each also have a local time zone ("timezone": "America/Los_Angeles") that can be different across documents.

I'm trying to do a date_histogram aggregation based on local time, not UTC or a fixed time zone (e.g., using the option "time_zone": "America/Los_Angeles").

How can I convert the timezone for each document to its local time before the aggregation?

Here's the simple aggregation:

{
  "aggs": {
    "date": {
      "date_histogram": {
        "field": "created_timestamp",
        "interval": "day"
      }
    }
  }
}

Answer 1

If you store another field that's the local time without timezone information it should work.

Take every timestamp you have (which is in UTC), convert it to a date in the local timezone (this will contain the timezone information). Now simply drop the timezone information from this datetime. Now you can perform actions on this new field.

Suppose you start with this time in UTC: '2016-07-17T01:33:52.412Z'

Now, suppose you're in PDT you can convert it to: '2016-07-16T18:33:52.412-07:00'

Now, hack off the end so you end up with: '2016-07-16T18:33:52.412Z'

Now you can operate on this field.

Answer 2

I'm not sure if I fully understand it, but it seems like the time_zone property would be for that:

The zone value accepts either a numeric value for the hours offset, for example: "time_zone" : -2. It also accepts a format of hours and minutes, like "time_zone" : "-02:30". Another option is to provide a time zone accepted as one of the values listed here.