Export kWh hourly statistics?

In an attempt to try to figure out if hour by hour debiting of our electricity would make sense, I would like to export kWh usage per hour, to crunch some numbers in something like Excel (eww).

Has anyone done this before?

stevenma's picture

Re: Export kWh hourly statistics?


Please could you explain your question and reasoning in a little more detail?  Whilst I monitor this board very frequently I'm not one of the experts; there are far more knowledgeable members than me but I was interested in your question.

The reason I responded is that I am in the process of having a 22kWp (~7kWp South & ~15kWp West) solar panel & two inverter system installed on my UK commercial office building.  The 15kWp system was switched on late on Friday and as you might expect the bulk of our electricity consumption, c200 - 250kWh per 24hr day, Mon - Friday, 0830 - 1800hrs at two rate; day & night.

I can see from our half-hourly weekend consumption that already some of those half-hours were down to zero and we were almost certainly exporting electricity - something I had anticipated but don't wish to do as we can hopefully use it to pre-heat two 6kW immersion heaters for our toilet hand-washing hot water, amongst a couple of other possibilities.

As it happens, as well as the essential mandatory generation meter we are having a self-imposed export meter (system >30kWp in the UK must have an export meter) installed as part of the project so that I can accurately report to one tenant who has 25% of the building and pays that percentage towards the overall building's electricity bill.  The other tenants have the electricity wrapped up in their rent as we're a business centre operation.  There is no sub-metering.

I am planning to build the solar PV diverter / router described in much excellent detail elsewhere on this site and forum.  I have already built the EmonTx for a similar system at home where I have a ~4kWp solar system.



o_cee's picture

Re: Export kWh hourly statistics?

In Sweden, we are able to get our electricity bill based on an hourly spot price from Nord Pool (http://www.nordpoolspot.com/Market-data1/Elspot/Area-Prices/ALL1/Hourly/). This isn't about solar power at all.

Normally, we're billed monthly based on an average spot price, but I would like to run our real consumption against the fluctuating prices to see if it would be cheaper for our consumption or not.

AFAIK, EmonCMS only stores daily kWh data, therefore I'd need to extract it from the power table instead, this is what I would need help with. (I want to to this for old data, so adding new input filtering won't help). I'm pretty sure though that the raw pulses are stored as well (the pulses difference process doesn't clean up after itself), maybe that would be easiest to use.

Any SQL ninjas out there? :)  (no I haven't migrated my setup to Timestore yet)

o_cee's picture

Re: Export kWh hourly statistics?

Here we go, this seems to do the trick. feed_14 is a table with all raw pulse counts.

SELECT ((MAX(data) - MIN(data))/1000) as kWh,  HOUR(FROM_UNIXTIME(time)) as Hour, DATE(FROM_UNIXTIME(time)) FROM feed_14 GROUP BY HOUR(FROM_UNIXTIME(time)), DATE(FROM_UNIXTIME(time)) ORDER BY `time` DESC LIMIT 720;

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.