Having set up a sensor for both gas and electricity, the next step for me was to store the data.
There are countless solutions available for this either local or remote ("in the Cloud").
Probably the most famous one is Xively (which used to be COSM and before that Pachube). Trouble is they went really commercial without any offers for small projects. I tried a few alternatives but decided that I didn't want to publish my data and therefore turned to a local solution.
There are loads of possibilities for data storage, from the classical SQL solutions (MySQL, SQLite, ...), NOSQL solutions. A system which is gaining popularity is Graphite and its Whisper database. In the end, I decided to go for the simplest and probably most used database in the world of monitoring: RRD.
Almost all opensource monitoring and reporting tools on this planet use RRD. It has a big avantage, the database never ever grows! You lose the details (and even the data) over time but with the right setting this wasn't an issue. For me, its biggest drawback is that (except with Datastores as 'Gauge') EVERYTHING IS A RATE PER SECOND.
Some calculation is necessary first to convert the pulses into actual usage, then into a rate and when displaying into a meaningfull usage figure.
1 pulse = 10 dm3 = 10 litres
Easy... Well how do I store this?
First let's chop the time into 5 minute slots (this the usual base for RRD BTW) and let's count the pulses within a slot. At the end of the slot, one can push the number of litres:
rrdtool update gas.rrd 1407153600:100
Internally, RRD will not store the value 100 but 0.3333333333 because it is 100 per 5 min or 100 per 300 sec.
When consolidating (to a 30 min = 1800 sec period), RRD will average all the rates.
If I want to display the gas usage over a given period (5 min, 30 min, 1 day), I then need to multiply the value returned by the length of this period. Resp. 300, 1800, *86400.
A lot of people convert the values into Watt-Hour. Unfortunately the conversion is NOT straightforward and depends on a Correction Factor you can only discover a posteriori on your bill or if lucky on your provider website like this one. Cubic meters are OK for me.
- Power (rate): litre/s
- Energy: m3 per period
1 pulse = 1 rotation = 3.6 Wh
Like for Gas, we slice the time into the same 5 minutes slots and count the pulses within a slot. But remember that RRD stores rates per seconds. So to make sense we have to convert the Watt-Hour in Watt-Second:
1 Wh = 3600 Ws 3.6 Wh = 12960 Ws
Hence 1 rotation of the disk equals 12960 Ws. RRD will automatically store the value in Watts!
- Power: Watt
- Energy: Wh for display per period
As mentioned before, RRD stores the data in a way which means the values will be aggregated over time and you'll loose the original individual values. After thinking a lot about what to do and how to handle it, I settled for the following pattern:
- Step = 5 min = 300 sec (default anyway)
- Base RRA (storing Primary Data Points ):
- 2 hours => 24 rows
- Mainly for debugging and for "realtime" view
- 1/2 hour resolution:
- 2 years = 2 * 365 * 24 * 2 => 35064 rows
- Main view
- 1 day resolution:
- 5 years => 1826 rows
- Archiving as well as daily summary
RRD creation parameters
rrdtool create gas.rrd --step 300 \ DS:gas_usage:ABSOLUTE:600:0:U \ RRA:AVERAGE:0.9:1:24 \ RRA:AVERAGE:0.9:6:35064 \ RRA:AVERAGE:0.9:288:1826 rrdtool create electricity.rrd --step 300 \ DS:electricity_usage:ABSOLUTE:600:0:U \ RRA:AVERAGE:0.9:1:24 \ RRA:AVERAGE:0.9:6:35064 \ RRA:AVERAGE:0.9:288:1826