These are a couple of my continuous queries in Influxdb:
CREATE CONTINUOUS QUERY cq_electricity_values_1h ON ha BEGIN SELECT integral(power) / 3600 AS energy_1h, integral(cooker) / 3600 AS cooker_energy_1h, integral(boiler) / 3600 AS boiler_energy_1h, min(power) AS min_power_1h, max(power) AS max_power_1h, max(cooker) AS max_cooker_1h, max(boiler) AS max_boiler_1h, mean(tension) AS mean_tension_1h, stddev(tension) AS stddev_tension_1h, min(tension) AS min_tension_1h, max(tension) AS max_tension_1h INTO ha."30_days".electricity_values_1h FROM ha."25_hours".electricity GROUP BY time(1h) END
CREATE CONTINUOUS QUERY cq_electricity_1d ON ha BEGIN SELECT integral(power) / 3600 AS energy_1d, integral(cooker) / 3600 AS cooker_energy_1d, integral(boiler) / 3600 AS boiler_energy_1d, min(power) AS min_power_1d, max(power) AS max_power_1d, mean(tension) AS mean_tension_1d, min(tension) AS min_tension_1d, max(tension) AS max_tension_1d INTO ha.autogen.electricity_1d FROM ha."25_hours".electricity GROUP BY time(1d) TZ('Europe/Athens') END
They take instant power readings from ‘power’ (whole house), cooker, boiler and calculate their energy use by hour (1st CQ) and by day (2nd CQ) and store them in new values. I do some more, similar CQ for some Broadlink SP3S switches.
You can also calculate the energy integrals directly in Grafana from the instant readings in Influxdb. I don’t do it this way because I do the calculations in Influxdb anyway.
This is my query in Grafana, as shown in Query inspector:
SELECT cumulative_sum("energy_1h") FROM "30_days"."electricity_values_1h" WHERE time >= 1520546400000ms
The time filter is calculated by Grafana, I select ‘Today so far’.
Grafana has the added benefit that you can select values for today so far, this week, this month etc., which I haven’t been able to do in Influxdb directly.
I hope this is clear!
