Power to kWh/Day, input processing problem

Submitted by 3chooks on Sun, 08/12/2013 - 21:18

Hi,

Why are my kWh/d values so widely inaccurate (>400kWh / day) when the power consumption feed is accurate?

DASHBOARD with example of erroneous kWh/D values http://emoncms.org/lgrange

'Power' measurements from emonTXs successfully arrive as 'Inputs' on emoncms.org (every 5 seconds)

I created three feeds per power measurement to process and log power usage data as below.

# Process Arg

1 Allow positive 0

2 Log to feed TOTAL_Power

3 Power to kWh/d kWhd TOTAL_Power

4 histogram Hist TOTAL_Power

The 'TOTAL_Power' feed correctly records and displays current power consumption in dashboards etc.

The 'kWhd TOTAL_Power' feed records hundreds of kWh's, far in excess of actual consumption (10kWh/day)

All other measured power feeds processed this way produce a similar order of magnitude error.

Help, I had this working just fine previously and can't understand what is going wrong in the input processing?

Thanks in advance

Andrew

Re: Power to kWh/Day, input processing problem

Submitted by xrayman (not verified) on Fri, 27/12/2013 - 13:41.

Hi Andrew

I thought I was doing the maths wrong but I am not certain after reading your post if I am. I wrote an SQL script to extract the "power" from my power feed then broken that data up over 24 hrs based on my usage tariff. All fine until I then realised that my kwh figures were significantly lower than those produced by the visualizer for kwh or kwh/d.

Did you get anywhere figuring this out?

Revisiting my SQL I realised I was taking the average Watts per period of time then taking the average again which was clearly wrong.

Re: Power to kWh/Day, input processing problem

Submitted by xrayman (not verified) on Fri, 27/12/2013 - 14:02.

FYI - and this is most probably wrong knowing me! This should work in most MySQL idles - change the @date, @period_n_X and @cost as appropriate. If you are on a single tariff then you can just use section 7 or the final sql select. Also remember to rename the feed table as appropriate.

set @date='2013-12-25';
set @period_1_st='00:00:01';
set @period_1_fn='02:00:00';
set @period_2_st='02:00:00';
set @period_2_fn='13:00:00';
set @period_3_st='13:00:00';
set @period_3_fn='16:00:00';
set @period_4_st='16:00:00';
set @period_4_fn='20:00:00';
set @period_5_st='20:00:00';
set @period_5_fn='22:00:00';
set @period_6_st='22:00:00';
set @period_6_fn='23:59:59';
set @cost_1='0.06';
set @cost_2='0.18';
set @cost_3='0.06';
set @cost_4='0.18';
set @cost_5='0.06';
set @cost_6='0.18';
set @cost_6='0.18';
set @cost_7='0.13';

DROP TABLE off_on_breakdown;

CREATE TEMPORARY TABLE IF NOT EXISTS off_on_breakdown
SELECT
'1' as period,
AVG(data) as watts,
(UNIX_TIMESTAMP(concat(@date,' ', @period_1_fn)) - UNIX_TIMESTAMP(concat(@date,' ', @period_1_st))) as duration,
@cost_1 as rate
FROM emoncms.feed_1
where
time between UNIX_TIMESTAMP(concat(@date,' ', @period_1_st)) and UNIX_TIMESTAMP(concat(@date,' ', @period_1_fn));

insert into off_on_breakdown
SELECT
'2' as period,
AVG(data) as watts,
(UNIX_TIMESTAMP(concat(@date,' ', @period_2_fn)) - UNIX_TIMESTAMP(concat(@date,' ', @period_2_st))) as duration,
@cost_2 as rate
FROM emoncms.feed_1
where
time between UNIX_TIMESTAMP(concat(@date,' ', @period_2_st)) and UNIX_TIMESTAMP(concat(@date,' ', @period_2_fn));

insert into off_on_breakdown
SELECT
'3' as period,
AVG(data) as watts,
(UNIX_TIMESTAMP(concat(@date,' ', @period_3_fn)) - UNIX_TIMESTAMP(concat(@date,' ', @period_3_st))) as duration,
@cost_3 as rate
FROM emoncms.feed_1
where
time between UNIX_TIMESTAMP(concat(@date,' ', @period_3_st)) and UNIX_TIMESTAMP(concat(@date,' ', @period_3_fn));

insert into off_on_breakdown
SELECT
'4' as period,
AVG(data) as watts,
(UNIX_TIMESTAMP(concat(@date,' ', @period_4_fn)) - UNIX_TIMESTAMP(concat(@date,' ', @period_4_st))) as duration,
@cost_4 as rate
FROM emoncms.feed_1
where
time between UNIX_TIMESTAMP(concat(@date,' ', @period_4_st)) and UNIX_TIMESTAMP(concat(@date,' ', @period_4_fn));

insert into off_on_breakdown
SELECT
'5' as period,
AVG(data) as watts,
(UNIX_TIMESTAMP(concat(@date,' ', @period_5_fn)) - UNIX_TIMESTAMP(concat(@date,' ', @period_5_st))) as duration,
@cost_5 as rate
FROM emoncms.feed_1
where
time between UNIX_TIMESTAMP(concat(@date,' ', @period_5_st)) and UNIX_TIMESTAMP(concat(@date,' ', @period_5_fn));

insert into off_on_breakdown
SELECT
'6' as period,
AVG(data) as watts,
(UNIX_TIMESTAMP(concat(@date,' ', @period_6_fn)) - UNIX_TIMESTAMP(concat(@date,' ', @period_6_st))) as duration,
@cost_6 as rate
FROM emoncms.feed_1
where
time between UNIX_TIMESTAMP(concat(@date,' ', @period_6_st)) and UNIX_TIMESTAMP(concat(@date,' ', @period_6_fn));

# Total Over Period - Remove from Summation if calculating total daily cost
insert into off_on_breakdown
SELECT
'7' as period,
AVG(data) as watts,
(UNIX_TIMESTAMP(concat(@date,' ', @period_6_fn)) - UNIX_TIMESTAMP(concat(@date,' ', @period_1_st))) as duration,
@cost_7 as rate
FROM emoncms.feed_1
where
time between UNIX_TIMESTAMP(concat(@date,' ', @period_1_st)) and UNIX_TIMESTAMP(concat(@date,' ', @period_6_fn));

SELECT
    period,
    watts,
    watts / 1000 as kW,
    round(duration),
    round(duration / 3600) as hours,
    (watts / 1000) * (duration / 3600) as kWh,
    rate,
    round((watts / 1000) * round(duration / 3600) * rate,
            2) as cost
from
    off_on_breakdown;
/*
# Total over period independent check
SELECT
    AVG(data) as watts,
    AVG(data) / 1000 as kW,
(UNIX_TIMESTAMP(concat(@date,' ', @period_6_fn)) - UNIX_TIMESTAMP(concat(@date,' ', @period_1_st))) as duration,
    round( (UNIX_TIMESTAMP(concat(@date,' ', @period_6_fn)) - UNIX_TIMESTAMP(concat(@date,' ', @period_1_st))) / 3600) as hours,
    (AVG(data) / 1000) * ((UNIX_TIMESTAMP(concat(@date,' ', @period_6_fn)) - UNIX_TIMESTAMP(concat(@date,' ', @period_1_st))) / 3600) as kWh
from
    emoncms.feed_1
where
time between UNIX_TIMESTAMP(concat(@date,' ', @period_1_st)) and UNIX_TIMESTAMP(concat(@date,' ', @period_6_fn));
/*

Archived Forum