I have a data source which stores one numeric value per second into an InfluxDB table. I’m using Grafana to visualise the data, and I can see the values there.
I need to create a “running total” of the sum of these values over the past X seconds (I want to have at least three time intervals - the past 10 minutes (600 seconds), the past hour (3600 seconds) and the past 24 hours (86400 seconds)), calculated each second.
Firstly, is a continuous query in Influx the right way to go about this, or can someone suggest a better solution (either in Influx or in Grafana)?
Secondly, if a CQ is the right approach, what am I doing wrong with the following (because it doesn’t work)?
CREATE CONTINUOUS QUERY hourly ON telegraf
RESAMPLE EVERY 1s FOR 1h
SELECT sum(price) INTO hourly FROM mqtt_consumer GROUP BY time(1h), *
If I’ve read the documentation* right, that should create a new value every second (“EVERY 1s”) and calculate the sum over the past hour (“FOR 1h”).
But, if I then search for calculated values, I get just a single result:
select * from hourly
time host sum topic
1555426800000000000 systemy.customer.net 1.500003 PBX/Test
Waiting several seconds, or even a few hours, results in no further values being added to the “hourly” table.
Can anyone see what I’m doing wrong, or point me at an alternative solution?
*I’m using https://docs.influxdata.com/influxdb/v1.7/query_language/continuous_queries as my guide in all this