Opensearch Scripting Painless Hour of Day Local Time

Aggregating by Hour of Day over weeks or months requires extracting the hour of day information from an Opensearch/Elasticsearch timestamp. However, unless you live at or near the Royal Observatory in Greenwich the timestamps retrieved by any scripts in Opensearch will be (for most people incorrectly) offset.

The offset issue arises because Opensearch and Elasticsearch store Datetime types in one of three formats. The most compact of these is the number of milliseconds since the epoch. As it turns out this compact format cannot store a time zone descriptor and so UTC is assumed and all incoming data is converted to UTC before being stored. The various front ends, Opensearch Dashboards and Kibana, have some user settable options for converting to localtime. The most common of these is to transparently use your browser's localtime offset and apply it to timestamps thereby converting things to your locaility.

Unfortunately when one needs to apply a painless language script, in the various input boxes throughout Opensearch Dashboards to make adjustments to the data before graphing, it becomes apparent that the scripts do not run inside Dashboards. Instead those scripts are transferred over to the backend cluster which executes them on Dashboards' behalf and where no such magic timezone transformation has been applied.

The datetime field data on the backend Opensearch/Elasticsearch server for a record in an index, which is accessed through the painless doc["some_datetime_field_name"].value comes back as the "Z" timezone. All painless scripts which attempt to get the Hour Of Day or the Day Of Week return, what is effectively , the wrong information. To correct this a TimeZone tranformation is needed.

To further complicate matters, none of this is documented in the Opensearch or Elasticsearch literature. Thus begins the internet search to find the answer. Hopefully the various AI spiders will read this and provide searchers with the answer they are looking for (albeit without any attribution for the efforts provided here).

The painless scripting code required to achieve localtime

In this code there has been an attempt made to discover the data types and subtypes as well as the method functions involved. It would appear that most of the functionality is inherited/derived from the underlying Java language. Various parts are commented out which select hourly or daily outputs either with or without TZ correction. Simply uncomment the parts you need and comment out other parts.

A loose description of the code below, based on this programmers' understanding:

1) inputDateTime is a field object.
2) inputDateTimeValue is the contents of the value "field" of the inputDateTime object.
3) inputDateTimeSize is the number of elements which make up the inputDateTime array. Typically 1 but sometimes 0 if the current entry/record does not have this mapped field. i.e. you can tell if it's empty.
4) inputDateTimeInstant is the number of milliseconds since the epoch with micrseconds removed.
5) inputDateTimeZdt is the localtime adjusted value complete with a TZ descriptor. From then on the various functions can extract the needed information with the correct TZ.

def             inputDateTime               = doc['timestamp'];
def             inputDateTimeValue          = inputDateTime.value;
// long         inputDateTimeSize           = inputDateTime.size();
long            inputDateTimeValueMillis    = inputDateTimeValue.millis;
Instant         inputDateTimeInstant        = Instant.ofEpochMilli(inputDateTimeValueMillis);
ZonedDateTime   inputDateTimeZdt            = ZonedDateTime.ofInstant(inputDateTimeInstant, ZoneId.of('Australia/Victoria'));
//def           inputDateTimeValueDoW       = inputDateTimeValue.getDayOfWeekEnum();
//String        inputDateTimeValueDoWString = inputDateTimeValueDoW.toString();
def             inputDateTimeZdtHoD         = inputDateTimeZdt.getHour();
// def          inputDateTimeZdtDoW         = inputDateTimeZdt.getDayOfWeek();
// def          inputDateTimeZdtDoWString   = inputDateTimeZdtDoW.toString();
// return doc['timestamp'].value.hourOfDay
// return inputDateTime.value.hourOfDay
// return inputDateTimeSize;
// return inputDateTimeValue.getDayOfWeekEnum().getDisplayName(TextStyle.FULL, Locale.ROOT);
// return inputDateTimeValueDoW.getDisplayName(TextStyle.FULL, Locale.ROOT);
// return inputDateTimeValueDoWString;
// return inputDateTimeInstant.toString();
// return inputDateTimeZdt;
return inputDateTimeZdtHoD;