Querying time series within specific time interval per day

influxql
influxdb
#1

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.

#2

@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
#3

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

#4

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.

#5

@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?

#6

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.

#7

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