Elasticsearch query to filter based on time (and ignore date)

Grafana Elasticsearch
1

Hi,

for a panel I want to filter my elastic search data source to only get entries which where created in a specific time span.

As example I want to filter the field createdAt (which is a date time field) between 6 and 10am.

Of course this needs to be seperat from the selected time range from the panel (or dashboard). As example this means when I select a time range of one month In my panel I see entries for the whole month which were created between 6 and 10am of each day.

I have seen a lot of great questions which go into the same direction but they were all unanswered. Things I was looking for was “scripted fields”, “raw query editor” or ways to do this in the query but the elasticsearch suppot in grafana seems too limited to do this.

Any help would be appreciated, also “not possible” would also be a helpful answer :slight_smile:

2

By a lot of work and also by chance I was able to find a solution using Transform (with the newst grafana version 10.1.0, some transform operations were not available in my old version).

First we need to create a Query just with Query Type raw data.
Now in the transformations we need to copy the time field, there seems no default support for copying so I did it this way:

  1. Add the date time field with “Add field from calculation” by adding the time date field with the value 0
  2. Now we use “Convert field type” to parse and convert the number field:
    1. convert to time (with no input format)
    2. convert to string, use HH as date fromat (to select a date fromat I had to choose to convert to a time and put in the Input format, then I selected String again and the date fromat was selected)
    3. convert to number

Now we have sucessfully created copy of the date time field and extracted the hours from it.
Now we can strip our normal date time field off the time by just converting it to a string with Date frormat YYYY-MM-DD and then back to a time.

We can also “Filter data by values” now and filter the time field by whatever time we want.

Finally we do “Group by” and group by the date time field (whch is now the date only) and count a field (e.g. id)

Done, hope it helps somebody

2 Likes