ElasticSearch Term Aggregation script timezone conversion

Question

I'm really struggling with this. In Painless, how would I update the following:

 "aggs": {
    "total_messages_per_day_of_week": {
      "terms": {
        "script": {
          "lang": "painless",
          "source": "doc['date'].value.dayOfWeek"
        }
      },

To convert the UTC datetime in doc.date to a locale (e.g. 'America/Los_Angeles') BEFORE getting the day of week?

Basically I want to aggregate by day number, but where day number represents the desired timezone day, not UTC day.

Many thanks in advance!

Answer 1

You'd do it like this by first transforming your UTC date into a ZonedDateTime via Instant.atZone() and then taking the day of the week:

Instant date = Instant.ofEpochMilli(doc['timestamp'].value);
ZonedDateTime zdt = date.atZone(ZoneId.of('America/Los_Angeles'));
return zdt.getDayOfWeek().getValue();

And since doc.date.value is actually JodaCompatibleZonedDateTime (i.e. a delegate of ZonedDateTime), in your aggregation you can try this:

{
  ...,
  "aggs": {
    "days": {
      "terms": {
        "script": "doc['timestamp'].value. withZoneSameInstant(ZoneId.of('America/Los_Angeles')).getDayOfWeek().getValue()"
      }
    }
  }
}