Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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',
              },
            },
          },
        },
      },
    },
like image 819
Oliver Lloyd Avatar asked Nov 07 '16 22:11

Oliver Lloyd


2 Answers

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"
              }
            }
          }
        }
      }
    }
  }
}
like image 80
slawek Avatar answered Oct 02 '22 16:10

slawek


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

like image 26
Val Avatar answered Oct 02 '22 17:10

Val