Log tariff alongside current data

[nightcustard]

My electricity supply is dual rate (ie) I'm charged a day rate and a night rate. I've been looking into how to (fairly) accurately calculate how much a particular piece of equipment is costing me. I need to apply the correct tariffs, which won't necessarily be the same as the current ones if I'm looking at historical data. My other variable is the times of day when the tariffs cut in and out. My researches so far suggest that the best way to do this would be to record the tariff data alongside the current measurements, making grafana display quite simple (which is a must as far as I'm concerned(!)). Is this something that could be realised in the code or would I be better doing something in Grafana?

[fruffygutss]

ooo yes I have the same question! following

[David00]

Since the rates are going to inevitably change over time, you'll want to store the actual cost of the energy used in the database, like you were suggesting.

It's possible to do this with some modification to the Python code, OR, via InfluxDB through the use of continuous queries.

I'd suggest going down the InfluxDB CQ route just because the Python side of things doesn't actually have direct access to the energy figures (without querying). (By energy, I mean the figure that represents power * time, denoted in kWh, which is what we typically pay for).

An influxDB CQ automatically runs every X duration (whatever you set it to), and stores the output value from the period specified in a new table. The continuous query would have to calculate the energy over the CQ interval, then apply the cost for that energy based on the time of the measurements, then store it to a dedicated measurement for the costs. The only thing I'm unsure of at this point is how to determine which rate applies to the energy value, since you can't select by time in the CQ.

[nightcustard]

Thanks David, I'll investigate this. My brief look so far confirms you can't select by time but maybe I can run two queries with the two rates and then select the outputs by time downstream. That said, at this stage I don't even know how to run an InfluxDB query. It's a bit like attempting to write some doggerel in Welsh with only a Welsh phrasebook to help! As the old phrase goes, it's a learning opportunity... cheers, Mike

[David00]

I was hoping in the CQ you could maybe do two subqueries that return tarrif1 * total kWh during tarrif1's time interval and another one for tariff2 * total kWh during tariff2's time interval, but I'm not too sure about subqueries in a CQ.

To get an InfluxDB shell so you can run queries, you'd have to execute the following docker command on your Pi:

docker exec -it influx influx

Definitely a learning opportunity! If you want to give it a go in Python and need some guidance on how the data makes its way into the DB, I can provide an overview for that too. But, for the time being, the DB info in the Wiki (which provides an overview of the schema and even some sample queries) might be helpful: https://github.com/David00/rpi-power-monitor/wiki/Software-5.-All-About-the-Database

[nightcustard]

Many thanks for the pointers. I'll certainly give it a go 😀

On Mon, 29 Nov 2021 at 18:50, David00 @.***> wrote:

I was hoping in the CQ you could maybe do two subqueries that return tarrif1

• total kWh during tarrif1's time interval and another one for tariff2 * total kWh during tariff2's time interval, but I'm not too sure about subqueries in a CQ.

To get an InfluxDB shell so you can run queries, you'd have to execute the following docker command on your Pi:

docker exec -it influx influx

Definitely a learning opportunity! If you want to give it a go in Python and need some guidance on how the data makes its way into the DB, I can provide an overview for that too. But, for the time being, the DB info in the Wiki (which provides an overview of the schema and even some sample queries) might be helpful:

https://github.com/David00/rpi-power-monitor/wiki/Software-5.-All-About-the-Database

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/David00/rpi-power-monitor/issues/40#issuecomment-981916430, or unsubscribe https://github.com/notifications/unsubscribe-auth/AGEPRBWSXFTVQKKJVUYGQ7LUOPDOVANCNFSM5IW24WLQ . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[nightcustard]

Hi David - I've created a couple of CQs which are generating the cost data and it looks as if 'Flux' may be the way to go to select the right data over the time intervals I need. However, I got stuck trying to change the default influxdb config, which has flux-enabled = false. I've created an influxdb.conf file in /etc/influxdb with appropriate permissions but I think I need to tell influxdb to use it on start up (which I haven't yet figured out how to do). I'd be grateful for any suggestions!

[David00]

Hi @nightcustard, that sounds interesting that Flux might be the way to go. I haven't yet looked into Flux but I know that's the direction that InfluxDB is moving towards.

Since InfluxDB runs inside a Docker container, all of the config files are inside of the container itself. You'll need to get a shell inside the container, and then make the changes to the /etc/influxdb/influxdb.conf file inside the container. To do this, run the following command, which executes bash in interactive mode in the container named influx:

docker exec -it influx bash

Now that you're inside the container environment, you can navigate to /etc/influxdb and edit the influxdb.conf file there. I think the only file editor available in the container is vi, which has a little bit of a learning curve, so definitely check out some "how to's" on YouTube if you're not familiar with it.

[nightcustard]

Thanks David. I'll have to pause on this one as I've come across an unrelated problem - My Pi 4 is responding slowly (possibly after a power cut a couple of days ago). Normal service is resumed with another SD card containing a backup image so it looks as if the current SD has been corrupted in some way. There are some error messages appearing in the influx log every few minutes complaining about 'cannot allocate memory', which may have a bearing on the slowdown. Anyway, I'm going to try backing up the current database and restoring it into the replacement SD card - the backup is progressing as I type.

[nightcustard]

I'm looking into this issue again and am trying to decide which way to go - python or CQ. What's tipping me back towards a python solution is that my power distribution system has a power output which only comes live when the cheap rate is active. I can very easily hang a low voltage (circa 3v dc) wall wart on that supply and connect the output to an unused GPIO input on the Pi, thus giving me a '1' or '0' depending upon the active tariff. It looks easy to add some code to the rpi-power-monitor programme to detect what state the GPIO input is in and it could get the relevant tariff from a tweaked config.py. Is storing the tariff in the influx db any more complicated than adding a line to the 'infl.write_to_influx(' section? I'm assuming there's more to it than that (it always is!) but I'll ferret away and see how I get on. I might still revert back to CQs as any solution along those lines can be applied retrospectively, of course.

[David00]

I really like your idea of using a GPIO to detect when the cheap rate is active. That's super cool! If you do that, make sure to use a resistor in between the positive lead from your 3V DC power supply and the Pi's GPIO (10k-12k should be fine) just to limit the current provided by your power supply. If this specific outlet isn't near your Pi, you could also deploy an ESP8266 or ESP32 board to monitor the outlet and send an HTTP request to the Raspberry Pi over your Wi-Fi to tell the Pi that the cheap rate is in effect (with some additional development on the Pi, of course).

Adding a new datapoint to store in Influx is fairly straightforward on its own. I added some preprocessing steps to the way I'm loading all the power monitor data into Influx and created a custom Point() class to help keep things organized, but none of that is actually required to get data into Influx.

You'll just need to create a dictionary with the measurement, fields, and time keys, stick the dictionary inside a list, and then pass the list to client.write_points(). Here's an example from the docs: https://influxdb-python.readthedocs.io/en/latest/include-readme.html#examples

And here's an example from my code: https://github.com/David00/rpi-power-monitor/blob/8bf33ddfca2a67b27a26e6524502648a949e01ac/influx_interface.py#L81-L88

You can call client.write_points() from the main power-monitor.py file (if you want to) because the entire influx_interface.py file is imported into power-monitor.py as infl. You'd simply have to reference the client object in power-monitor.py as infl.client.write_points()

[nightcustard]

I'm glad to be able to report success!

Hardware-wise, I've got a small wall-wart power supply powered from the power breaker which is only active when the night rate is in force. This is connected to GPIO17 via a simple buffer using a 2N7000 FET and a couple of resistors.

Software-wise, I've forked your code* and added appropriate GPIO input detection as well as the necessary additions to add the tariff data into the appropriate measurements. This should all be visible in the 'Tariff_additions' branch in my repository https://github.com/nightcustard/rpi-power-monitor/tree/Tariff_additions

I've put the day & night tariff rates in the config.py file and they're picked up by power-monitor, which selects the appropriate rate according to the state of GPIO17. I want to check the cost of the equipment monitored by a particular ct as well as the cost of the whole load. My reading of the influxdb query language is that you cannot reference data from more than one measurement in a query, so I've added a 'tariff' field key to both the 'home_load' and 'raw_cts' measurements, which is a little inefficient I suppose but it saves me having to learn how to use 'Flux'!

I then run a continuous query per ct and one for the 'home_load' total (eg)

ct0_cost CREATE CONTINUOUS QUERY ct0_cost ON power_monitor BEGIN SELECT last(tariff) * integral(power) / 3600000 INTO power_monitor.autogen.ct0_cost FROM power_monitor.autogen.raw_cts WHERE ct = '0' GROUP BY time(10m) END home_load_cost CREATE CONTINUOUS QUERY home_load_cost ON power_monitor BEGIN SELECT last(tariff) * integral(power) / 3600000 INTO power_monitor.autogen.home_load_cost FROM power_monitor.autogen.home_load GROUP BY time(10m) END [Update Note: I need to optimise the Group by Time setting - probably 1m or 10s might be better for accuracy/finer granularity but it'll possibly have to be balanced against processor load. Experimentation awaits!

I can then display the results in Grafana :-))

Many thanks again for pointing out the way. BTW, I've also transferred over to an 'endurance' type SD card as you suggested and I'm also running the Pi from a mains UPS, so I've added 'nut-client' to achieve an orderly shutdown before the UPS cruds out.

• I'm rather unsure of the protocol involved in forking other people's code, so if I've breached said protocol it was of course unintentional and please let me know!