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.
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â)
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
