In Elasticsearch, how can I apply a timezone to a scripted date operation?

Question

With the aggregation below and using ES5, I'd like to get the dayOfWeek & hourOfDay based on a given timezone (supplied as an identifier from the TZ database).

How can I edit "doc['created'].date.dayOfWeek' to adjust for the offset?

    aggs: {
      dayOfWeek: {
        terms: {
          script: {
            inline: "doc['created'].date.dayOfWeek",
            lang: 'painless',
          },
        },
        aggs: {
          hourOfDay: {
            terms: {
              script: {
                inline: "doc['created'].date.hourOfDay",
                lang: 'painless',
              },
            },
          },
        },
      },
    },

Tremolite

Answer 1

Something like this should work:

{
  "size": 0,
  "aggregations": {
    "dayOfWeek": {
      "terms": {
        "script": {
          "inline": "doc['created'].date.setZone(org.joda.time.DateTimeZone.forID(tz)); doc['created'].date.dayOfWeek",
          "lang": "groovy",
          "params": {
            "tz": "Europe/London"
          }
        }
      },
      "aggs": {
        "hourOfDay": {
          "terms": {
            "script": {
              "inline": "doc['created'].date.setZone(org.joda.time.DateTimeZone.forID(tz)); doc['created'].date.hourOfDay",
              "lang": "groovy",
              "params": {
                "tz": "Europe/London"
              }
            }
          }
        }
      }
    }
  }
}

You will probably need to enable inline scripting for groovy by adding script.engine.groovy.inline.aggs: on to the elasticsearch.yml file. See: This discussion.

Note. The above won't work with painless because it is locked down and does not allow you to edit the whitelist..

Lowenstern

This gives me the error: Variable [DateTimeZone] is not defined. even with org.joda.* in my java.policy file. I'm no java dev but sn't java.time.zone already included by default? (Based on: elastic.co/guide/en/elasticsearch/reference/master/…) – Tremolite

I've modified to include the fully-qualified class name. The date fields are Joda DateTime instances, hence why I'm not using the normal Java TimeZone class – Lowenstern

That gives me Variable [org] is not defined. I've modified the whitelist by creating /usr/local/etc/elasticsearch/.java.policy and putting grant { permission org.elasticsearch.script.ClassPermission "org.joda.*"; }; in it but maybe ES isn't picking this up? It's installed via Homebrew. – Tremolite

The .java.policy file doesn't go into the ES home folder. If you have an elasticsearch user, you can put it in /home/elasticsearch, otherwise you can specify it on the command-line or simply modify the system-wide java.policy file in $JAVA_HOME/lib/security/java.policy. Check this again to pick the best option for you – Lowenstern

I have it set as a system policy in ${JAVA_HOME}/lib/security/java.policy and as an env var setting ${ES_JAVA_OPTS} -Djava.security.policy=.... No dice. – Tremolite

Instead of painless, can you specify groovy instead? See this issue – Lowenstern

Val, you're a superstar. So yes, using groovy did the trick. I had to also enabled inline scripting but with this I'm up and running. Thank you! – Tremolite

painless? I think not. – Tremolite

Answer 2

Found solution using painless. Because they are migrating elasticsearch from Joda to native java.time, the support for Joda is not good in painless.

{
  "size": 0,
  "aggregations": {
    "dayOfWeek": {
      "terms": {
        "script": {
          "inline": "Instant.ofEpochMilli(doc.created.date.millis).atZone(ZoneId.of(params.tz)).dayOfWeek",
          "params": {
            "tz": "Europe/London"
          }
        }
      },
      "aggs": {
        "hourOfDay": {
          "terms": {
            "script": {
               "inline": "Instant.ofEpochMilli(doc.created.date.millis).atZone(ZoneId.of(params.tz)).hour",
               "params": {
                  "tz": "Europe/London"
              }
            }
          }
        }
      }
    }
  }
}

Pulsatory

"inline": "Instant.ofEpochMilli(doc.timestamp.date.millis).atZone(ZoneId.of(params.tz)).format(DateTimeFormatter.ofPattern('e HH'))", Worked well for me; (<Day Hour> buckets) thanks for the painless example :) – Revelation

They are also deprecating "inline". Use "source" instead. – Alanna

Instant.ofEpochMilli(doc.timestamp.getValue().getMillis()).atZone(ZoneId.of(params.tz)).format(DateTimeFormatter.ofPattern('E HH')) is the ES7 variant .... – Revelation