David Morris – brassedoff.net

I’ve got my CurrentCost php graphs to a point where they’re suitable for a wider audience. The graph takes data from the mySQL database that’s holding the load data coming in from the CurrentCost sensor and display, does a little bit of massaging (all statistically acceptable!) and generates a dynamic graph.

Currently, the graphing routine is automatically scaling. I’m not sure long term whether this is the best option or not. It would probably be better to take a long term maximum and stick with that, but as the scaling rounds up to the nearest 1000 watts above the daily maximum, it should cope with all eventualities except the kettle AND the microwave AND the oven all on at the same time (and the electricity meter going in to overdrive!).

I’ve also added a tweak that allows me to go backwards and forwards a day at a time on the graph.

If you want to see the current load graph, look here. As this isn’t linked to the date or time of this blog posting, depending on when you look at it, there may not be much data on it. A better view would be to look at yesterday (which is always yesterday relative to YOUR today).

There are still a few more things needed, and a proper wrapping page would be nice, when I can get around to it. I’ve also still got the cost calculations to add in (daily cron job just after midnight and results going to another table perhaps?). Also, I’ve not yet given a lot of thought to what’s going to happen when we go into / out of daylight savings, but that’s not for a couple of months yet!

If you want the PHP that’s producing these graphs, it’s available here. (It comes with a  full Government health warning).

I’ve just spent the last hour or so playing about with graphing the data from our CurrentCost data collection. It’s not brilliant – more work is definitely needed, but I can now at least see the data trends.

Mark Phelan has sent me some SQL queries to have a play about with to consolidate the data (who needs figures ever six seconds?!) and James Taylor has (quite correctly) suggested using RRDTool to graph the data. I’ll probably play about with the home-grown stuff for a while longer then give up and go over to RRDTool.

I spent the duration of my bike ride home thinking about kilowatt hours, joules and watts. More of this to come. It’s essential to work out the cost electricity from the simple watts data coming from the CurrentCost.

Hmmm…

Apologies for the non-technical readers. There. That’s that out of the way.

Before I disappeared off to Dusseldorf last week, I wrote about the ’scarey gadget’ that we’d acquired: the CurrentCostpower monitor. Whilst I was away, the data cable for this came, so I plugged it in to my openSuSE 11.1-based server for a quick play. Linux found the active cable straight away and proclaimed it to be /dev/ttyUSB0. A quick check with minicom confirmed that to be the case and we were in business.

The CurrentCost spits out an XML data chunk every six seconds with device time, watts, temperature and a few other bits and pieces in it. It’s a simple setup. You don’t need to poll the device. It’s a one-way dialog.

Thinking about the best way to get the data into a usable form, although it’s not my main area or expertise by any stretch, PERL seemed to be the answer. I wasn’t really bothered about doing a full parse job on the XML. I only wanted to pick out the temperature, watts and time (and I’ve even ended up throwing that away).

The data is getting shoved in to a mySQL database which I’ll play about with using a bit of PHP in days to come. For now though, here’s the PERL script that’s doing the work.

#!/usr/bin/perl -w

use DBI;

$dbiDSN         = "dbi:mysql:energy";
$dbiUser        = "energy";
$dbiPassword    = "password";

$dbiInsert      = "INSERT INTO energy_use (temperature,watts) values (?,?)";
$dbh = DBI->connect($dbiDSN, $dbiUser, $dbiPassword) or die $DBI::errstr;

$dbiStatement = $dbh->prepare($dbiInsert);

$device = "/dev/ttyUSB0";

$createTable = "create table energy_use (recordtime timestamp primary key, " .
                "temperature float, watts integer)";

open SENSOR, $device or die 'unable to open sensor';

while (1) {
$line = <SENSOR>;
$line =~ /<watts>(\d+)<\/watts>/;
$watts = $1;
$line =~ /<time>(\d+\:\d+\:\d+)<\/time>/;
$time = $1;
$line =~ /<tmpr>([0-9\.]+)<\/tmpr>/;
$tmpr = $1;
print "$time : $watts : $tmpr\n";

$dbiStatement->execute($tmpr, $watts);

}

Like I said, PERL isn’t my strong point, but it seemed like the correct tool for the job. I’m using the DBI handler for mysql. You can play about with the various bits to configure the script in the first few lines.

All I’ve attempted to do to parse the XML stream is grab the data in between teh relevant start and end tags. It’s not pretty and wouldn’t stand up to a scenario where several sensors were in use on different devices as supported by the CurrentCost, but it’s a start.

The living proof of the thing working can be seen in the database:

mysql> select * from energy_use;
+---------------------+-------------+-------+
| recordtime          | temperature | watts |
+---------------------+-------------+-------+
| 2009-07-18 17:44:38 |        22.8 |  1786 |
| 2009-07-18 17:44:44 |        22.8 |   591 |
| 2009-07-18 17:44:50 |        22.8 |   591 |
| 2009-07-18 17:45:02 |        22.8 |  1886 |
| 2009-07-18 17:45:08 |        22.8 |  1846 |
| 2009-07-18 17:45:14 |        22.8 |  1802 |
| 2009-07-18 17:45:20 |        22.8 |   591 |
| 2009-07-18 17:45:26 |        22.8 |  1496 |
| 2009-07-18 17:45:32 |        22.8 |  1762 |
+---------------------+-------------+-------+

Obviously, this is going to collect a lot of data, so a cron job to purge out old data would also be appropriate at some point. As you can see, I’ve thrown away the time from the CurrentCost data in favour of a mySQL timestamp which seemed more logical and less code because I’d still have needed to get a date from somewhere…

It’s official. I have in the house now the scariest gadget imaginable for something of a gadget freak. It’s this little gem – a CurrentCost CC128.

It tells us how much electrickery we’re using and worse, how much it costs. So far, we’ve knocked about £10/month off the bill by being a bit more careful about what we leave switched on. £10/month payback on a £40 investment. That’s a bit of a no-brainer.

There’s more though. This gadget as a serial interface. And there’s a USB serial cable available for it. And it squirts data out in XML format.

There are already several people writing open source software for it, and when I get back from my latest business trip, guess what I’m going to be doing?!

I sense a little bit of Java or PERL as a daemon process, a mySQL table or two and a bit of PHP on the horizon.

For the record, the device has a little battery-powered sensor that clamps around the output from the domestic meter and communicates with the base station using 433MHz wireless. The base station itself is mains powered, estimated at a couple of pence per week to run and in ideal circumstances will pick up up to 30m away from the sensor.

I can’t wait.

No, it’s not a green or environmental post. It’s a techie post. I’ve been exchanging tweets recently with my ISP over their record bandwidth usage lately. The cause of these records? Tennis. Well, indirectly, tennis. In actual fact, BBC iPlayer. The Murray quarter final match yesterday afternoon accounted for a massize 25% of Plusnet’s bandwidth at something like 1.8Gb/s. It’s probably a good job they brought another 155Mb pipe on recently.

There’s a lot of discussion going off in the press at the moment about who should pay. Some advocate pushing charges back to the content provider, but they’re arguing that they’ve paid their whack in getting their server farm connected to the Internet in the first place with the necessary high capacity pipes.

As we look more to Internet delivered services, this argument is going to rumble on and on and users like thee ‘n’ me are going to want to make sure we’re using an ISP that can make the necessary investments in bandwidth to ensure consistent delivery of service. The situation is only going to get worse. Virgin will sell you a 50Mb fibre connection to home. Plusnet and others are trialling or have already rolled out ADSL2+ with up to 20Mb on copper.

Is, therefore, the current charging model the right one moving forward? Commentators more skilled than me will probably be having that argument for years to come. In the meantime, I’ll be catching up with TopGear from the weekend tonight on iPlayer and I hope there’s no tennis on to pinch the bandwidth!