Home Automation Domotica Forum Europe, Bwired Forum

I measure my energy usage using a wattson which is polled every 10 seconds using openwattson. Since openwattson cannot write to the database directly I use a txt file to gather the readings and parse that with a php script which is called every minute.

I want to store the readings in a mysql db per hour, but I'm unsure how to calculate the usage per hour. Do I just sum up the readings per 20 seconds?

Below is a sample of the readings:

The interval between the readings doesn't really look like 10 seconds; more like 5-20 seconds? But looking at the numbers I think this is the momentary power usage, not cumulative.
I assume the readings are W.

When you have 3600 readings per hour (=3600 seconds) of 1, that would mean you used 1W during an hour, so you used 1 Wh ((3600*1)/3600).
Or when you have 1800 readings per hour of 1, that would also mean that you used 1W during an hour, so you used (1800*2)/3600) 1 Wh.

So all you have to do is multiply every reading (248, 226 etc) with the real interval in seconds between current and previous reading and sum that. And at time **:00 you write that value/3600 (storage interval) to the db and you have the number of Wh . And reset the sum afterwards, of course.

Hi Robert,

thank you for your reply. You're right, the interval is about 20 seconds, I made a typo.

I initially planned to keep a record per hour in my db using ON DUPLICATE UPDATE in the insert statement. Every reading would check if there was a reading for that hour and than summed the two up.

I now I think I'll log every interval reading and calculate the average use per hour at the start of the next hour. That way I can check how many readings there were in that hour, sum them up and divide them through the number of readings. And I can display the current use by displaying the last reading.

Or is there a better way of doing this in mysql?

Sounds fine to me!