Tips for InfluxDB and Grafana when using IotaWatt

This post is a collection of tips, in no particular order. It follows on from Iotawatt, InfluxDB and Grafana Setup which is a step-by-step. Do that first, then tweak with these.

Snippets for Influx


# Delete a CT
delete from iotawatt where ct=~/Circuit.*/ and device='iotaa39'
# View first or last readings (change "DESC")
select * from iotawatt where device='iotaa39' order by desc limit 10
# Copy from one to another. Include the GROUP BY * to get tags across
SELECT * INTO master1.autogen.source FROM master2.autogen.dest GROUP BY *
# Fix up a measurement if you're stuffed it by adding fields and tags of the same name!
# Copy just the fields, group by the tags and put into a temp measurement. Then delete the old and replace.
select PF::field,Watts::field into iotawatt_fixed from iotawatt group by ct::tag,device::tag,units::tag
drop measurement iotawatt
select Watts,PF into iotawatt from iotawatt_fixed group by ct,device,units

Add tags to existing measurements.

See this comment and adapt. For many complex (or not!) operations, it’s necessary or easier to use a client (e.g. python script). Careful not to blow up your machine – you’ll need to batch the process by date.

Time Periods with Influx

Importing and Exporting to CSV

# Send a CSV to influx. Check the UTC v local timezone is correct!
python ./csv-to-influxdb.py -s live.phisaver.com:8086 -u bbeeson -p imagine --dbname iotaanne -m iotawatt -tc time --fieldcolumns Volts,Watts --tagcolumns ct,device,units -i iotaanne.csv -tf %Y-%m-%dT%H:%M:%SZ --fieldname Watts


# export from influx to csv
influx -username bbeeson -password ***** -precision rfc3339 -execute "select * from tmp" -database master1 -format csv > iotaanne.csv


Integral and the missing fill(0)

Say your logging goes down, with the last read on a ct (call it fridge) at 100W. You are down for 1 week. If you do an integral, influxdb will give you the ‘wrong’ number. Specifically, it will take 100W and assume it’s always on…

This will add 0 Watts where gaps are…

select Watts into iotawatt from (select Watts from (select sum(Watts) as Watts from iotawatt group by time(1h),ct,units,device fill(0)) where Watts=0 group by *) where ct!='' group by *

… so that this CQ doesn’t over-cook the integral, which it does because fill(0) is not respected.

SELECT integral(Watts, 1000h) AS kwh INTO iotaa39.autogen.energy FROM iotaa39.autogen.iotawatt GROUP BY time(1h), ct, device,units fill(0)