David Morris – brassedoff.net

Conscious as I have been for a while that I’ve been collecting a lot of records in my CurrentCost energy log table, I thought I’d better do something to tidy it up a bit as I had a bit of spare time this morning.

A quick check showed a gnats over 1.4million records in the log file (one record every six seconds since July) so something needed to be done! My collector script doesn’t add the per-record cost to the data, so I have to run a quick query first to update the ’slice_cost’:

update energy_user set slice_cost = watts * 0.0016667 / 100 * 0.175

(yes, I know I should cancel some of the zeroes out)

I wanted a table summarised by hour showing total watts and cost per hour. My base table is this:

mysql> describe energy_use;
+-------------+----------+------+-----+---------------------+-------+
| Field       | Type     | Null | Key | Default             | Extra |
+-------------+----------+------+-----+---------------------+-------+
| recordtime  | datetime | NO   | PRI | 0000-00-00 00:00:00 |       |
| temperature | float    | YES  |     | NULL                |       |
| watts       | int(11)  | YES  |     | NULL                |       |
| slice_cost  | double   | YES  |     | NULL                |       |
+-------------+----------+------+-----+---------------------+-------+
4 rows in set (0.00 sec)

I’m not really interested in temperature, so I wanted this for a summary table…

mysql> describe total_energy;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| rdate       | date    | YES  |     | NULL    |       |
| rhour       | int(11) | YES  |     | NULL    |       |
| total_watts | int(11) | YES  |     | NULL    |       |
| total_cost  | double  | YES  |     | NULL    |       |
+-------------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Now let me point out here, I’m not an SQL expert by any stretch of the imagination, so forgive me if this is obvious to some people. An aggregate query on the energy_use table would give me the summarised data I wanted, and wrapping that up with an insert query would summarise and build the total_energy table in one fell swoop:

insert into total_energy (rdate, rhour, total_watts, total_cost)
 select  date(recordtime) as rdate, hour(recordtime) as rhour,
  sum(watts) as total_watts, sum(slice_cost) as total_cost
  from energy_use
  group by date(recordtime),hour(recordtime);

…which appears to do the job quite nicely.

All I have to do now is decide how much data I want toremove from the detail table. Realistically, I don’t need anything before October now I have a summary table, so

delete from energy_use where date(recordtime) < '2009-10-01'

…which removed 989000 records (or thereabouts).

Next job is to write some graphs to display the cost profiles…