Dear all,
i have a database who contain a lecture of the instant watt consumption of my house. Every 10 sec the device read the real consumption and save it in the database. I like to extract haw many watt i consume in thi day in this week and this month.
The data is locate here: W,domain=sensor,entity_id=potenza_reale
Can you help me with the influxdb string for extract this data?
Thanks in advance.
No idea for help me?
I think you can sum your values over an hour and divide by the number of 10s intervals in an hour to get kWh. Then you can just add these up over whatever interval to get the result in kWh/interval. Let me know if it works, as I’m unsure it is correct:
1 day:
select sum(*) from (select sum("value") / 3600 from watts group by time(1h)) group by time(1d);
7 day:
select sum(*) from (select sum("value") / 3600 from watts group by time(1h)) group by time(7d);
Ok in this way is ok, but if the controller some times don’t send the value this is missing. Can I divide instead of 3600 by the exact number of received value?
I think you could use count("value") on the inner query:
select sum(*) from (select sum("value") / count("value") from watts group by time(1h)) group by time(1d);
It might also be useful to look into using the fill option, particularly fill(previous).
Ok this string seams to be very useful:
select sum(*) from (select sum(“value”) / count(“value”) from “W” group by time(1h)) group by time(1d);
and what I have to add to this if I want a total consumption in one certain period?
Something like this:
AND time >= ‘2018-04-29’ AND time <= ‘2018-04-30’
There is way to do this or it’s impossibile?
Try adding the time range to the Wh calculation and then remove the 1d grouping:
select sum(*) from (select sum("value") / count("value") from "W" group by time(1h) where time >= '2018-04-29' AND time <= '2018-04-30');
Try this one (group by goes after where):
select sum(*) from (select sum("value") / count("value") from "W" where time >= '2018-04-29' AND time <= '2018-04-30') group by time(1h);
