Querying time series within specific time interval per day

Hello everyone, what is the best way to query influx for time series happened between 9 am and 3 pm each day for say one week / month at time? Latter is same as filter away all time series from 3 pm today until 9 am next day and then for some period of time like one week or month.

Thank you.

@adis.dela

I have the same exact question. What is the proper way to filter on a literal time-of-day? The following query, unfortunately, does not work:

SELECT last(value) from abc where time>‘2017-12-01’ and time>‘09:00:00’ group by time(1d,15h) tz(‘America/Chicago’)

Neither does this (it actually runs, but the result is wrong):

SELECT last(value) from abc where time>‘2017-12-01’ and time>9h group by time(1d,15h) tz(‘America/Chicago’)

1 Like

Yes, we are also trying to do something similar. Does anyone have any ideas about how to do this?

We had the same requirement: exclude non working time from some queries. Eventually we did it by adding two tags to every datapoint: “hour-of-day” and “day-of-week”. The series cardinality goes up by a 24*7 factor, but in our case it is still manageable and with the new releases it shouldn’t be an issue anymore, I hope.

@Liviux Did you have to add the tags at your data-insert level or could you run some sort of continuous query that can add that tag?

As you know, on Influx you can’t really “add” tags to an existing datapoint, but you must insert a new datapoint with the same data plus the new tags. We did it at an intermediate step where data is aggregated in a complex way on hourly basis. Due to the kind of business logic applied, we couldn’t do it with a proper continuous query but had to code it in a AWS Lambda function triggered every hour.

Makes sense. That’s probably what we will end up doing too