Time of Day filter

[stevezemlicka (Stephen Zemlicka)]

I am trying to produce a dashboard that has some visualizations but also some data tables. In order to produce the results I need in the data tables, there are times that I must filter based on the time of day. For example, while my date range may be Jan1@00:00-Jan31@23:30, I need to further limit the visualizations to 16:00-21:00.

I've been looking at:

How to filter date field by days and hours separately

you just take the minute into account as well, then you can model sth like 9:30. The date is always stored as UTC, you need to calculate offsets yourself.

But I can't seem to apply a similar DSL filter to our dashboard. Is there a way to apply a filter above and beyond the built-in date range filter?

[lukas (Lukas Olson)]

Sure, you should be able to filter by using the "add filter" dialog when creating the data table visualization:

Let me know if that helps.

[stevezemlicka (Stephen Zemlicka)]

Excellent. I was able to confirm and replicate. The one difficulty I see here is this would seem to require a filter entry for each day. This may not be a problem for shorter analysis but if we were to want to pull up a month or a year, it would be difficult to create one of these for each day (though theoretically possible).

Is there a way to ignore the day and just specify a time? I tried editing in DSL using an * for the day without success. I also noticed that there is a date and a date.keyword option which is the raw date data that gets mapped to @timestamp so I tried a date.keyword of "16:00" but that didn't work either.

I'm wondering if I'll need to create a new field with the hour as an integer so I can filter by that? I wonder if I can use a scripted field for this.

[stevezemlicka (Stephen Zemlicka)]

Scripted field HourOfDay()

Hi, I want a field containing the hour of the day for each elasticsearch document. The easiest way would be to parse @timestamp into a hour_of_day field but that means I would have to reindex all my data. I saw another solution by using Scripted Fields with: doc["@timestamp"].getHourOfDay() That works to get the hour ('HH') in format 00-23 and that's exactly what I was expecting. But, the hour_of_day field isn't correct as it shows a different hour value (probably due to timezone difference…

I tried to create a scripted field for this using the above info but seem to be having difficulty.

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"
}
}
}
]
}

and this:

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]."
}
}
}
]
}

[stevezemlicka (Stephen Zemlicka)]

My solution to this ultimately was to create a scripted field that pulled the hour from the @timestamp

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 { "roo…

Then create a control visualization that allowed to select the hours we wanted to include.