ElasticSearch scripted field: subtract Epoch timestamp and ISO datetime

ElasticSearch allows putting data into date-time fields in different formatting. You may have one field that was put as a numeric Epoch time (Unix / POSTIX time), and another that was put as an ISO datetime.

fieldEpoch: 1621616091
fieldISO: 2021-05-21T15:04:05Z

Then, you might want to create a field script that would subtract one from the other.

Luckily, ElasticSearch allows putting the data in different formatting, but internally, it always stores them as Epoch time (long number). Therefore, it offers a way to simply get the date as Epoch, regardless of the formatting that the date was stored as: doc["fieldISO"].value.millis

One can use this functionality in a scripted field, to subtract the two fields:

if (!doc['fieldEpoch'].empty && !doc['fieldISO'].empty) {
return doc['fieldEpoch'].value - doc['fieldISO'].value.millis;
} else {
return 0;
}

The output of this would be the difference between the two fields, in milliseconds. It might be convenient to divide by 1,000 to switch to seconds difference:

if (!doc['fieldEpoch'].empty && !doc['fieldISO'].empty) {
return ((doc['fieldEpoch'].value - doc['fieldISO'].value.millis) / 1000);
} else {
return 0;
}

But notice that dividing eliminates the precision point (2,600 milliseconds will become 2 seconds instead of 2.6 seconds).

Tip: If you’re using Kibana, to create this as a scripted field, go to Management > (Kibana) Index Patterns > (choose your index) > Choose the tab “Scripted field” > Click “Add scripted field”. You can preview that the script works by clicking “Get help with the syntax and preview the results of your script.” and then “Preview results” tab.

Java, Golang, PHP, JavaScript, Databases