Filter on @timestamp

Hello team,

There is some timeseries data in an index that is captured every 4 minutes. So there are 15 entries every hour. Below is the sample data.
|@timestamp| No.of hits |
|April 12th 2018, 09:00:00.000|36|
|April 12th 2018, 09:04:00.000|37|
|April 12th 2018, 09:08:00.000|34|
|April 12th 2018, 09:12:00.000|36|
|April 12th 2018, 09:16:00.000|39|
|April 12th 2018, 09:20:00.000|37|
|April 12th 2018, 09:24:00.000|36|
|April 12th 2018, 09:28:00.000|38|
|April 12th 2018, 09:32:00.000|37|
|April 12th 2018, 09:36:00.000|37|

What I'm trying to do is get the data for the last 90 days so I use a range query. But I want to filter out the date so that I get hourly data and not in between entries. That is one entry per hour let's say at 8am, 9am, 10am etc... as below
|@timestamp| No.of hits |
|April 12th 2018, 09:00:00.000|36|
|April 12th 2018, 10:00:00.000|42|
|April 12th 2018, 11:00:00.000|55|
|April 12th 2018, 12:00:00.000|38|

But the wild card or regex query will not filter @timestamp field. Can you tell me how to achieve this?

Thanks

hi @krsna,

I'd consider writing a scripted-field that turns your timestamp in a string-field or integer field with just the minutes. And then, when it is on the hour, you can filter on that scripted-field.

here's more info on scripted fields: https://www.elastic.co/guide/en/kibana/current/scripted-fields.html

You'll probably want to write a painless script. You can find out more info about that here:https://www.elastic.co/guide/en/elasticsearch/painless/master/index.html

ok Thomas, seems like a good workaround. I'll do it. Thank you.

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