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',
              },
            },
          },
        },
      },
    },

score 8 · Answer 1 · answered Oct 19 '17 at 13:20

8

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"
              }
            }
          }
        }
      }
    }
  }
}

answered Oct 19 '17 at 13:20

slawek

2,709
1
25
29

"inline": "Instant.ofEpochMilli(doc.timestamp.date.millis).atZone(ZoneId.of(params.tz)).format(DateTimeFormatter.ofPattern('e HH'))", Worked well for me; ( buckets) thanks for the painless example :) – David Goodwin Sep 03 '18 at 14:37
2

They are also deprecating "inline". Use "source" instead. – YeeHaw1234 Dec 04 '18 at 20:41
Instant.ofEpochMilli(doc.timestamp.getValue().getMillis()).atZone(ZoneId.of(params.tz)).format(DateTimeFormatter.ofPattern('E HH')) is the ES7 variant .... – David Goodwin Apr 18 '23 at 15:57

score 6 · Accepted Answer · edited Nov 11 '16 at 22:27

6

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

edited Nov 11 '16 at 22:27

Oliver Lloyd

4,936
7
33
55

answered Nov 10 '16 at 12:43

Val

207,596
13
358
360

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: https://www.elastic.co/guide/en/elasticsearch/reference/master/modules-scripting-painless.html#painless-api) – Oliver Lloyd Nov 10 '16 at 19:15
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 – Val Nov 10 '16 at 19:23
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. – Oliver Lloyd Nov 11 '16 at 09:11
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](https://www.elastic.co/guide/en/elasticsearch/reference/current/modules-scripting-security.html#_customising_the_classloader_whitelist) to pick the best option for you – Val Nov 11 '16 at 09:19
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. – Oliver Lloyd Nov 11 '16 at 10:40
Instead of painless, can you specify groovy instead? [See this issue](https://github.com/elastic/elasticsearch/issues/20942#issuecomment-253862586) – Val Nov 11 '16 at 13:52
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! – Oliver Lloyd Nov 11 '16 at 22:21
painless? I think not. – Oliver Lloyd Nov 11 '16 at 22:27

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

2 Answers2

Linked