Timestamps in Painless

In short: Converting a UTC timestamp to a local timestamp (in Switzerland).

Long story: I was facing the use case, that for a statistical analysis I aggregated data from a minute basis to a hourly basis. The date aggregation itself rendered UTC timestamps, since everything in Elasticsearch is stored in UTC. I have to calculate my local timestamp in Switzerland, in order to display the correct occurrence in Kibana. My solution involves in the transform part painless. Painless scripting in Elasticsearch allows the usage of the new Java Time API since Java 7.

Therefore we can test the code in Java before:

Instant myInstant = Instant.ofEpochMilli(1503176400000L);

ZoneId switzerland = ZoneId.of("Europe/Zurich");
LocalDateTime localDateTime = LocalDateTime.ofInstant(myInstant, switzerland);

System.out.println(LocalDateTime.ofInstant(Instant.ofEpochMilli(1503176400000L), ZoneOffset.UTC)

The console output will give us:


item.key contains the aggregated timestamp. I create an Instant based on milliseconds and retrieve the milliseconds for my respective time zone.

"transform": {
    "script": {
      "lang": "painless",
      "source": """
def docs=[];
def id='';
def value=0;
for(item in ctx.payload.aggregations.trx_over_time.buckets) {
  def document = [
    '_id': item.key,
    '@timestamp': LocalDateTime.ofInstant(Instant.ofEpochMilli(item.key), ZoneOffset.UTC)
    'value': item.sum_trx.value,
    'logger': 'STA9101',
    'channel': 'Issuing',
    'ingest.time': ctx.execution_time,
    'ingest.agent': 'watcher'];
return ['_doc': docs];