Pete,
Now that I have influx running well and have uploaded a week of my home history to a database, I’ve had a chance to look into this question. I was able to produce daily kWh for all of my feeds, and you will see that it’s possible to do hourly, monthly whatever.
I use the INTEGRAL(field,1h) function, where the field is power in watts. The result is total Wh (watt hours, you need to divide by 1000 for kWh. The values I got match those produced by the IoTaWatt graph function using the cumulative kWh. The query I used was:
SELECT INTEGRAL(*,1h) FROM home WHERE TIME >= now() - 7d GROUP BY time(1d)
This returns the daily Wh for all fields for the last seven days. I could also ask for hourly Wh used by my heat pump for the past 24 hours:
select integral(heat_pump,1h) from home where time >= now() - 24h group by time(1h)
name: home
time integral
1521579600000000000 538.1373611111109
1521583200000000000 690.3091666666667
1521586800000000000 789.3097222222224
1521590400000000000 821.369583333333
1521594000000000000 1069.4304166666666
1521597600000000000 1044.0902777777787
1521601200000000000 1087.9373611111116
1521604800000000000 1217.8781944444443
1521608400000000000 1210.7416666666659
1521612000000000000 1188.0751388888891
1521615600000000000 1372.5412499999998
1521619200000000000 1053.476666666666
1521622800000000000 1395.1230555555558
1521626400000000000 1093.0806944444446
1521630000000000000 1391.4270833333328
1521633600000000000 1182.8416666666656
1521637200000000000 1218.0890277777783
1521640800000000000 993.070972222222
1521644400000000000 1065.8558333333333
1521648000000000000 1021.4786111111111
1521651600000000000 840.759444444445
1521655200000000000 832.3983333333333
1521658800000000000 795.7462500000004
1521662400000000000 805.4040277777781
1521666000000000000 51.02916666666668
I have a concept for using the calculator units designation to directly export various units like kW, watts, power factor, frequency, etc. When that’s done, you will be able to upload kW and cumulative kW if this INTEGRAL function doesn’t do it for you. It just seems like a waste to upload both the kW and watts when a decent time series database can do the integration.
duh! update - to get kwh instead of wh just up the INTEGRAL unit specification to 1000h
select integral(heat_pump,1000h) from home where time >= now() - 7d group by time(1d)
name: home
time integral
1520985600000000000 2.0544279166666692
1521072000000000000 18.62191999999996
1521158400000000000 19.322551944444378
1521244800000000000 22.387960138888914
1521331200000000000 29.076634166666636
1521417600000000000 26.828250138888947
1521504000000000000 24.464948472222254
1521590400000000000 23.058248472222253
Direct daily kWh from IoTaWatt
1521072000, 18.6
1521158400, 19.3
1521244800, 22.4
1521331200, 29.1
1521417600, 26.8
1521504000, 24.5
1521590400, 23.1