Access to data for other user applications

[nightcustard]

I've made a few stepper motor mechanical displays (see pics below) for things like 'real time' internet download & upload speeds and YouTube channel subscribers - these are driven from Raspberry Pi Zeros and get their data either from web page parsing or, in the case of internet speeds, querying my router. It would be really cool to make a mechanical display showing overall power usage and, possibly, total power used over the day.

How best to get the relevant data is my main unknown - streaming data in real time from Grafana looks like it could be rather complicated (if it's even possible) so I think I'll have to modify the power monitor code to extract instantaneous power and then totalise it over the day. I need the data at around one sample every ten seconds, so nothing tasking in terms of rates and accuracy isn't really important. Though I am far from a competent Python programmer, I'm reasonably confident I can modify the power monitor code to get the data samples I need.
This is all slightly complicated by the fact I'm still running the original power monitor code, slightly modified to add cost as a parameter.
Any kind people willing to share their thoughts?

[David00]

That's so cool, @nightcustard!!

This is all slightly complicated by the fact I'm still running the original power monitor code, slightly modified to add cost as a parameter.

Yes, that's going to get in the way eventually... fortunately, I've been doing a lot of work on v0.3.0 and it's almost ready for the actual non-beta release.

I'd recommend you go through the backup process so you don't build on top of "old" stuff. There are several improvements in v0.3.0 that make it worth upgrading, including an automatic backup utility, easier setup and calibration, automatic downsampling, and more. The upgrade process is unfortunately very involved, but I've written thorough instructions to upgrade that have been tested by a couple of other users. In general, you should generate a backup of your power monitor database, flash the v0.2.0 image, run the migration script to migrate your old data to the new numbering scheme (which also creates downsampled data points that make it easier to query against), and then upgrade to v0.3.0-beta.

So, in summary:

1. Follow the Influx Backup Procedure that I wrote in this comment. Just make sure to export the database backup to keep it safe.
2. Flash the v0.2.0 image to a new microSD if you have one, or overwrite your existing microSD card, assuming that you have backed up the changes to the code that you made, just in case!
3. In your fresh v0.2.0 image, start at step 3 of the Upgrade and Data Restoration Procedure in this comment.

---

To answer your question about how to get the data, though, I'd suggest writing a separate Python script to query the Influx database directly. In the new v0.3.0 docs I'm working on, I already have a page up about the database structure (here) and will have some examples on how to query shortly (the examples won't cover the Python aspect - it'll be direct Influx shell queries, which you can then take and piece together using the InfluxDB docs about their Python connector). You can also look at my db_migrate script for examples on how I used the Python Influx client to both get data out of the database, and insert it back into.

[nightcustard]

Hi David - I finally got around to migrating to v0.2.0 (using your pre-built image) and then to v0.3.0 beta - all OK so far. I then tried installing the Influxdb Python client - unfortunately things didn't go as smoothly(!):

$ pip install influxdb-client

ERROR: pip's dependency resolver does not currently take into account all the packages that are installed. This behaviour is the source of the following dependency conflicts.
requests 2.21.0 requires urllib3<1.25,>=1.21.1, but you have urllib3 1.26.14 which is incompatible.

I thought I'd better run this past you before I attempt to resolve the dependency :-)

Cheers, Mike

[nightcustard]

Hi David 😁 Plenty of research to be done - querying the database is not something I contemplated but is undoubtedly the best approach, so thanks for the pointers! Just as an aside, I modified the original power-monitor.py code to add a GPIO read which tells me when my off-peak rate is active. I added it as a parameter in the database to permit its use in Grafana. I know you've discussed ways of adding costs into the project - is something like this still on your radar? As soon as the new code is released I'll update my system (and then tweak it to add the off-peak stuff) When the meter is up and running, I'll post a pic 😁 Cheers Mike

…

On Fri, 17 Feb 2023, 02:41 David, ***@***.***> wrote: That's so cool, @nightcustard <https://github.com/nightcustard>!! This is all slightly complicated by the fact I'm still running the original power monitor code, slightly modified to add cost as a parameter. Yes, that's going to get in the way eventually... fortunately, I've been doing a lot of work on v0.3.0 and it's almost ready for the actual non-beta release. I'd recommend you go through the backup process so you don't build on top of "old" stuff. There are several improvements in v0.3.0 that make it worth upgrading, including an automatic backup utility, easier setup and calibration, automatic downsampling, and more. The upgrade process is unfortunately very involved, but I've written thorough instructions to upgrade that have been tested by a couple of other users. In general, you should generate a backup of your power monitor database, flash the v0.2.0 image, run the migration script to migrate your old data to the new numbering scheme (which also creates downsampled data points that make it easier to query against), and then upgrade to v0.3.0-beta. So, in summary: 1. Follow the Influx Backup Procedure that I wrote in this comment <#87 (comment)>. Just make sure to export the database backup to keep it safe. 2. Flash the v0.2.0 image to a new microSD if you have one, or overwrite your existing microSD card, assuming that you have backed up the changes to the code that you made, just in case! 3. In your fresh v0.2.0 image, start at *step 3* of the Upgrade and Data Restoration Procedure in this comment <#87 (comment)> . ------------------------------ To answer your question about how to get the data, though, I'd suggest writing a separate Python script to query the Influx database directly. In the new v0.3.0 docs I'm working on, I already have a page up about the database structure (here) <https://david00.github.io/rpi-power-monitor/docs/v0.3.0-beta/database-info.html#overview> and will have some examples on how to query shortly (the examples won't cover the Python aspect - it'll be direct Influx shell queries, which you can then take and piece together using the InfluxDB docs about their Python connector). You can also look at my db_migrate <https://github.com/David00/rpi-power-monitor/blob/upgrade-helpers/rpi_power_monitor/db_migrate.py> script for examples on how I used the Python Influx client to both get data out of the database, and insert it back into. — Reply to this email directly, view it on GitHub <#91 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AGEPRBVWO4GTOKWK7S5GDWDWX3QMPANCNFSM6AAAAAAU6FMXLI> . You are receiving this because you were mentioned.Message ID: ***@***.*** com>

[nightcustard]

I've got the analogue meter up and running, thanks to your tip re. the Influxdb Python client. It turned out to be pretty easy to interrogate the influxdb data, once I'd read up on how to convert Influxdb queries to flux queries.

For example, my Influxdb query in Grafana for current power is:

SELECT last("power") FROM "raw_cts" WHERE ("ct" = '1') AND time >= now() - 10s and time <= now()

In the Python client this becomes:

query1 = f'from(bucket: "power_monitor/autogen") |> range(start: -10s) |> filter(fn: (r) => r._measurement == "raw_cts" and r._field == "power" and r.ct == "1",) |> last()'

and my query for energy used today so far (the 'time' parameter comes from Grafana in this case):

SELECT integral("power") / 3600000 FROM "raw_cts" WHERE ("ct" = '1') AND time >= 1679184000000ms and time <= now()

becomes:

query2 = f'from(bucket: "power_monitor/autogen)" |> range(start: {day_start}Z, stop: now()) |> filter(fn: (r) => r._measurement == "raw_cts" and r._field == "power" and r.ct == "1",) |> integral(unit: 1s)'

Influxdbclient has a 'date.truncate' function (|> range(start: date.truncate(t: now(), unit: 1d), stop: now()) to get the start of the current day but I couldn't for the life of me find how to import the necessary 'date' package, so I used the following code to generate the same thing:

now=datetime.now() # returns (eg) 2023-03-19 13:39:41.804793 day_start = now.replace(hour=0, minute=0, second=0, microsecond=0) # returns (eg) 2023-03-19 00:00:00 day_start = day_start.isoformat() # returns (eg) 2023-03-19T00:00:00 return(day_start)

Note query2 returns energy in Watt seconds as I couldn't get the Python f string to accept a divide by 3600000 calc. I did the maths in Python after the query instead.

I've also attached a short video showing the prototype meter display in action. The meter was a quartz clock bought from a charity shop - I stripped out the gears inside the quartz module and rearranged them to allow a servo motor to drive each hand independently. The dial face is something I created in Powerpoint.

I hope to write up its construction as an 'Instructable' when time permits but if anyone wants some details in advance of that, please let me know and I'll see what I can do.

Powermeter_vid.mp4

[David00]

@nightcustard, that is so cool!! Thanks for sharing.

If you update your power monitor to v0.3.0, your query2 will become simpler. This is because in v0.3.0, I added the automatic creation of retention policies and continuous queries, so a new measurement that holds the actual kWh figure gets created. This means you don't have to do any math operations when executing the query. My docs on the subject are here.

Influxdbclient has a 'date.truncate' function (|> range(start: date.truncate(t: now(), unit: 1d), stop: now()) to get the start of the current day but I couldn't for the life of me find how to import the necessary 'date' package, so I used the following code to generate the same thing

I've been doing a lot of work with Flux lately and I also use the date.truncate Flux function. You can simply use import "date" to pull in the date package.

Seeing the hands on your display move with such precision is fascinating. Did you build the motor control board yourself too? I'd love to see your future Instructable because I'm curious about the process to calibrate your motors to the custom display.

[nightcustard]

Thanks! 😀 I've been playing around with v0.3.0 beta on a spare Pi4; I've forked the code to include my GPIO tariff monitoring and it seems to work well. I thought I'd wait updating my 'live' machine though until the code has been released so I only have to do one set of mods. The updates look very useful and well worth updating for. As for the import "date" command, it's no doubt academic given your v0.3.0 CQs but for my education, exactly what statement did you use to achieve this? I tried 'from influxdb_client import date' which failed with an 'ImportError' exception. None of my web searches were of any help. The stepper motor zeroing uses the tried and tested magnets and Hall effect sensors method - I glued some tiny magnets to the hands and my software stops each stepper when the respective Hall sensor detects the magnets. It's then a simple matter of applying an offset to drive each stepper to its arbitrary zero position. Calibration of the dial is done by trial and error - my scale is 300° so once I know how many steps it takes to drive each pointer a full 360°, it's easy to scale appropriately. I've discovered the cheap steppers I bought (£2 each, I think) aren't consistently precise, so an occasional zeroing is required. Cheers, Mike

…

On Tue, 21 Mar 2023 at 15:56, David ***@***.***> wrote: @nightcustard <https://github.com/nightcustard>, that is so cool!! Thanks for sharing. If you update your power monitor to v0.3.0, your query2 will become simpler. This is because in v0.3.0, I added the automatic creation of retention policies and continuous queries, so a new measurement that holds the actual kWh figure gets created. This means you don't have to do any math operations when executing the query. My docs on the subject are here <https://david00.github.io/rpi-power-monitor/docs/v0.3.0-beta/database-info.html#data-retention-and-continuous-queries> . Influxdbclient has a 'date.truncate' function (|> range(start: date.truncate(t: now(), unit: 1d), stop: now()) to get the start of the current day but I couldn't for the life of me find how to import the necessary 'date' package, so I used the following code to generate the same thing I've been doing a lot of work with Flux lately and I also use the date.truncate Flux function. You can simply use import "date" to pull in the date package. Seeing the hands on your display move with such precision is fascinating. Did you build the motor control board yourself too? I'd love to see your future Instructable because I'm curious about the process to calibrate your motors to the custom display. — Reply to this email directly, view it on GitHub <#91 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AGEPRBWQ2X2FBFF4SCGI6JTW5HFTHANCNFSM6AAAAAAU6FMXLI> . You are receiving this because you were mentioned.Message ID: ***@***.*** com>