... ...

Anagram Solver Solar Artwork

TuftedPuffin.co.uk

... ...

 

... ...
 

Live Data

Live

Today

Yesterday

Energy

PV

Electricity Usage

Write Up

Introduction

OneWire

Scripts

Mysql

Gnuplot

Energy

Theoretical

SVG

Behaviour

PV Monitor

Current Monitor

Bandwidth Monitor

Interfacing to a CC128 current Monitor

I have a Current monitoring device, it's made by "Current Cost". it's quite a nice little unit, with a good display and a wireless link to a current measuring device with a current clamp that clips over the Live cable going into the main electricity meter. It also has a USB lead that enables it to interface to a PC

I got it free when I changed to my electricity supplier.

While the unit itself is quiet neat, the software that came with it is pretty attrocious, being slow and unweildly, prone to crashing, and worst of all, only runs under Windows

As an experiment I tried plugging the USB lead into my Linux PC (Ubuntu 10.10). It was recongnised as a PL2303 USB to serial adaptor and allocated to the device /dev/ttyUSB0.

I used "cat

CC128-v1.290015421.6000077100448 CC128-v1.290015421.6000077100534

As it turns out "Current Cost" publish their output format, and it is available here

I created a Mysql database in the usual way and assigned permissions using "MYSQL Adminitstrator. The table was created with the following command :-

create table electricity ( date datetime, temperatire float, watts int );

A simple script to record the data was called for.

#!/bin/bash while read line do echo $line echo $line | awk -F '<|>' '{if( $16 != "hist" ) print "insert into electricity ( date,temperature, watts) values(\"" strftime("%F %R") "\", \"" $17 "\", \"" $35 "\"); COMMIT;"}' | mysql -u steve house mysql -u steve house today.out mysql -u steve house yesterday.out gnuplot ect.png gnuplot ecy.png ncftpput -u tuftedpuffin.co.uk -p xxxxxx ftp.tuftedpuffin.co.uk htdocs/Solar ect.png ncftpput -u tuftedpuffin.co.uk -p xxxxxx ftp.tuftedpuffin.co.uk htdocs/Solar ecy.png done <"/dev/ttyUSB0"

A few notes about the above script. I didn't want to go to the trouble of parsing the XML, and because the output is in a fixed format I adopted the approach of using the "<" and ">" as field delimeters for awk. If I do this then for the "Real-Time" output the temperature will be in field 17 and the watts in field 35. This will not be the case if it is a history output, in which caase field 16 will have the value "hist". So AWK will form an SQL Insert statement for the temperature and the watts but only for the real-time output, this is then simply piped into mysql to add the data to the database.

It should also be noted that I prefer to use my system time as opposed to the time from the CC128. This is because the CC128 clock doesn't seem to be that accurate and I don't want to keep setting it, whereas the clock on the computer is kept synchronised using NTP and so will always be right. Also the CC128 only outputs time, not date.

The data can be extracted from the database, for instance to get the watts usage for the day so far use an SQL command like this :-

select * from electricity where date > curdate(); /div>

This data can be plotted using GNUPLOT

load "default.p" set title "Electricity Usage (Today)" set ylabel "Watts" #set yr [10:75] set logscale y plot "today.out" using 1:($4>0?$4:1/0) title "Electricity" with lines

Where default.p is described elsewhere on this site

Note the use of a logscale for the y axis. On a linear scale the peaks dominate, using a logscale helps to give more detail to the lower values

Further work is still to be done to capture,process and store the historic data that comes from the unit, I am currently capturing the output data to file. Watch this space

Having now looked into the kwh per day data this seems reasonably easy to process, first job is to create an database table in which to store it :-

create table dayusage ( day date primary key, kwh float );

Making the day number a primary key makes it easier to avoid adding duplicate data, to extract and store the data from the daily history store, add this command to the monitoring script:-

echo $line | awk -F '<|>' '\ function printdays( day, kw ) \ { \ daynum = substr( day, 2 ); if( substr( day, 1, 1 ) == "d" && daynum > 0 && daynum < 90 )\ {\ newday = strftime( "%F ", systime() - substr( day, 2 ) * 86400 );\ print "INSERT IGNORE INTO dayusage VALUES (\"" newday "\"," kw ");";\ }\ }\ {\ if($16 == "hist" && $33 == "0" )\ { \ printdays( $36, $37 );\ printdays($40, $41); \ printdays($44, $45); \ printdays($48, $49); \ }\ }' | mysql -u steve house

I've split it across several lines to make it more readable, but it could just as well be on one line. I create a function for forming the SQL command because each line contains fourdays worth of data hence an SQL command has to be written out 4 times. Here I check that $33 is equal to 0, this is the sensor number, I only have one sensor and it is numbered 0.

Using the IGNORE option on the SQL INSERT command prevents mysql from throwing an error when an attempt is made to add a duplicate key (i.e. the day field) This is why we made day a Primary key, so that MYSQL would not be able to add more than one entry for any given day.

Again we use the system time to get the date, and subtract the number of days extracted from the XML fields to derive a date for a particular measurement.

This page is best viewed using Firefox Spread Firefox Affiliate Button

 

... ...