Keep the hour range and set the day dynamically

Elastic Stack Kibana
,
1

Hello everyone,

I've search for a long time, unfortunately I can't found any solution.
Is it any way to set dynamically the day but keeping the date range fix when I creat a Kibana visualisation ? (I don't have access to the Elasticsearch, only to the Kibana interface).

For more context :
I want to extract statistics using a verticalbars visualisation, these statistics are day by day statistics but the time range stay always fix, (for example 6AM-2PM, 2PM-10PM). So I wish to keep this hour range fix but that every day the date change automaticly.

Did you know if this is possible ? (I'm not effraid to use Query DSL filter if needed)

Thanks to all those who will try to help me. :slight_smile:

2

Suggestion: Runtime fields (eg hour of day extracted from your timestamp). Then filter on that ?

(Might be expensive)

3

Using ES|QL it looks something like this (for the 6AM to 2PM case):

FROM my_index  
  | WHERE @timestamp <=?_tend and @timestamp >?_tstart
  | STATS count = COUNT(*) BY OverTime = BUCKET(@timestamp, 100, ?_tstart, ?_tend)
  | WHERE DATE_EXTRACT("hour_of_day", OverTime) > 5
    AND DATE_EXTRACT("hour_of_day", OverTime) <= 14

Is that what you are looking for?
Mind that ES|QL currently works with UTC only, so tweak that accordingly.

1 Like
4

Thank you for your answers :slight_smile:

I'll do some test and come back to you with feedback

5

Hello,

Just to let you know, for now I didn't have the right to use ES|QL. I'm discussing with the stack holder to have the rights; else I will have to find another way or just drop this idea.

6

how did you get on?

Another idea, though maybe you won't have rights for it, would be to use an ingest pipeline to add the fields you want to aggregate on, eg hourOfDay, maybe also dayOfWeek, dayOfMonth, etc. These are simple extractions form the timestamp field, similar to the DATE_EXTRACT ESQL function. Obviously, if implemented, would only help with newly indexed data but ... another idea at least.

7

Hello @Torado

Adding one more idea to the list if it works for you :

Thanks!!

8

Hello @Tortoise and @RainTown !

First sorry for the response time, yesteday was a day off in my country.

For now I can't use ES|QL because the stack is in a older version where (from the stack holder) the feature is not stable, so he didn't enable it but he plan to update the stack in near future so I will be able to test in few time.

For you two new idea I will tried it and come back to you thanks :slight_smile: