Hour Scripted Field

I am trying to create a scripted field that is the hourly value of each document. The ultimate goal is to filter by specific hours of the day for multiple days (hence why I can't just use the date range on the dashboards).

After a bit of research, the best resource I found was the following which seems to indicate that this is possible:

However I cannot seem to get it to work. When I try to preview the results, I receive the following:

doc['@timestamp'].date.hourOfDay

produces

{
"root_cause": [
{
"type": "script_exception",
"reason": "runtime error",
"script_stack": [
"doc['@timestamp'].date.hourOfDay",
" ^---- HERE"
],
"script": "doc['@timestamp'].date.hourOfDay",
"lang": "painless",
"position": {
"offset": 17,
"start": 0,
"end": 32
}
}
],
"type": "search_phase_execution_exception",
"reason": "all shards failed",
"phase": "query",
"grouped": true,
"failed_shards": [
{
"shard": 0,
"index": "san-isabel",
"node": "erbPD7dEQPKwU5NNOXVB9g",
"reason": {
"type": "script_exception",
"reason": "runtime error",
"script_stack": [
"doc['@timestamp'].date.hourOfDay",
" ^---- HERE"
],
"script": "doc['@timestamp'].date.hourOfDay",
"lang": "painless",
"position": {
"offset": 17,
"start": 0,
"end": 32
},
"caused_by": {
"type": "illegal_argument_exception",
"reason": "Illegal list shortcut value [date]."
}
}
}
]
}

and this:

LocalDateTime.ofInstant(Instant.ofEpochMilli(doc['@timestamp'].value),ZoneId.of('America/Chicago')).getHour()

produces

{
"root_cause": [
{
"type": "script_exception",
"reason": "runtime error",
"script_stack": [
"LocalDateTime.ofInstant(Instant.ofEpochMilli(doc['@timestamp'].value), ZoneId.of('America/Chicago')).getHour()",
" ^---- HERE"
],
"script": "LocalDateTime.ofInstant(Instant.ofEpochMilli(doc['@timestamp'].value), ZoneId.of('America/Chicago')).getHour()",
"lang": "painless",
"position": {
"offset": 62,
"start": 0,
"end": 110
}
}
],
"type": "search_phase_execution_exception",
"reason": "all shards failed",
"phase": "query",
"grouped": true,
"failed_shards": [
{
"shard": 0,
"index": "san-isabel",
"node": "erbPD7dEQPKwU5NNOXVB9g",
"reason": {
"type": "script_exception",
"reason": "runtime error",
"script_stack": [
"LocalDateTime.ofInstant(Instant.ofEpochMilli(doc['@timestamp'].value), ZoneId.of('America/Chicago')).getHour()",
" ^---- HERE"
],
"script": "LocalDateTime.ofInstant(Instant.ofEpochMilli(doc['@timestamp'].value), ZoneId.of('America/Chicago')).getHour()",
"lang": "painless",
"position": {
"offset": 62,
"start": 0,
"end": 110
},
"caused_by": {
"type": "wrong_method_type_exception",
"reason": "cannot convert MethodHandle(Dates)JodaCompatibleZonedDateTime to (Object)long"
}
}
}
]
}

What might I be doing wrong and how might I accomplish this?

It might be helpful to provide a bit more context on my data. When ingesting, I am mapping a "date" field to @timestamp. So while I do have @timestamp, I also have a simpler date field in each document in the following format:

MM/DD/YYYY HH:mm

Perhaps I can pull it from that consistently formated field without relying on any fancy get functions?

return doc['@timestamp'].value.getHour();

This works for me.

3 Likes

YES!!! This worked for me as well. Thank you!

For those that come across this in the future, I had an issue with the previous solution and timezone. Seems this was matching UTC which didn't align with the expected output due to how timezones are handled with scripted fields. What ultimately worked was:

return LocalDateTime.ofInstant(Instant.ofEpochMilli(doc['@timestamp'].value.millis),ZoneId.of('America/Chicago')).getHour()

1 Like

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.