Watts to kWh, InfluxDB and Grafana

Support
1

In the UK we are in a bit of an energy crisis - 7 or 8 electricity providers have gone bust in the last 3 weeks. When this happens the Energy Regulator transfers the customers of the bust business to another provider - supply is uninterrupted but the rates are very different. I was on 12.7 pence/kWH three days ago, now it is around 22 pence (they haven’t confirmed the exact rate yet).

To encourage the sproglets (and wife) to be a bit more conscious I thought I would update our Grafana dashboard to show current cost per hour along with suitable daily, weekly and monthly info.

I think that the integral function of InfluxDB as a continuous query each hour (for hourly data) and daily (for daily data) should provide the kWH info but I can’t get the syntax to work properly.

Does anyone have IotaWatt → InflusDB → Grafana working to show cost? If so, please could you share your query/setup.

I think the query goes something like this:

CREATE CONTINUOUS QUERY kwh ON ilcibne RESAMPLE EVERY 1d FOR 30d BEGIN SELECT integral(Watts, 1000h) AS kwh INTO ilcibne.autogen.energy FROM ilcibne.autogen.iotawatt WHERE units = ‘Watts’ GROUP BY time(1h), ct, device fill(0) END

This comes from here: Iotawatt: Single Tenant InfluxDB and Grafana Setup – Brett Beeson it uses his database and measurement names - I suspect it is the syntax with the autogen retention policy that is the problem - but it might be something more basic.

Looking at the bright side of the energy price increase, the payback on the solar array will improve - every kWH that I generate and use in the house is saving me twice as much this week as last week. :slight_smile:

The end goal is a Grafana type dashboard - there might be a better way than using InfluxDB

Thanks for your help.

2

The Query in Grafana (you have to use the script editor not the graphical one - the graphical one wont accept (and deletes if you toggle it) the 1000h in the integral function.

SELECT integral(“Watts”,1000h) FROM “iotawatt” WHERE (“sensor” = ‘Consumption’) AND $timeFilter GROUP BY time(1d)

Works to give the kWH accurately for yesterday - the number is the same as using graph+ in IotaWatt and looking at the total in the stats - so that bit works.

Incidentally, this site https://phisaver.com/ (same person who wrote the blog I linked to earlier) has a fantastic dashboard in their ‘live demo’ link. It looks like they are installing IotaWatt and dashboards as a professional service.