How to compare 2 data sets with either an integral or derivative

I have a power meter reader that feeds data to NR through MQTT (reader -> 433MHz radio -> rtl_433 -> MQTT -> NR). Once the data is in NR, I process it and send it to InfluxDB for storage. The data comes in correctly so long as the meter itself is working. I've had issues with this reader a few times (initially water ingress, then battery failure, then failed unit, then a DOA replacement). The current unit is working great...right now. But, knowing the history I am leery of letting it run without a monitor.

My setup takes in each message from MQTT (one type of data per package) and process it independent of the rest of the flow. There are 4 types of data provided by the reader: current kW, accumulated kWh, temperature, battery status. In the past, the 2 ways the meter failed was by not sending data anymore or by sending invalid data (generally the same value repeatedly). I currently have 2 sections of my flow to attempt to capture these. The first watches one data type (instantaneous kW) and sends an email if it has not seen new data in a threshold period. The second watches the same data (instantaneous kW) and sends an email if the value does not change by more than 5% in a threshold period.

The first seems to work as it notified me when my second meter had a battery failure and stopped transmitting. The second set of code works to some degree, but it has a flaw. Because the reader looks at the time it takes for the wheel to spin, low usage will result in a very large "gap". The reader has a maximum gap size (65536 or ~65 seconds). Based on the way my meter is set up, the Kh is 24 and the resulting minimum reportable usage is 1.3kW (I won't get into the math, just trust me that it's correct). IOW, in the middle of summer when the day temperature never gets below where the furnace needs to kick on and it's not hot enough for me to want to worry about A/C there isn't much using power other than the water heater. Our typical usage at night is already below the 1.3kW threshold even in winter so in summer the instantaneous usage will basically just be a flat line at 1.3kW.

What I was thinking of doing is comparing the integral of instantaneous kW versus accumulated kWh in some way to confirm that the data was within reasonable limits and matched. I'm not sure if there is a guide on something like this but I haven't found it in my initial search. I'll continue looking but if anyone has any recommendations I'd appreciate.

I am using InfluxDB and I know there is an integrate function there so perhaps it would be easiest to try to use a query and let InfluxDB do the heavy lifting for integration? But how do I compare the 2 branches in the flow?

Wouldn't it be easier just to expect a change over a longer time, such that even at your lowest usage you would expect to see a change?

Personally I don't worry about the accumulated value. You can't use it for much anyway because it resets if you get a new meter. You can just use the instantaneous values and work out the total yourself (using the integral query).

So you are suggesting that I could look at the accumulated data and just assume that it should increase and, if it hasn't, then flag it? That's possible.

The instantaneous data is what I was planning on using for accumulated usage until I saw that my specific setup can only go down to 1.3kW. That is probably ok in winter since my furnace draws between 3 and 20kW depending on what it's doing. However, in summer with nothing particularly drawing it would result in significant overestimation. The accumulated usage is correct so I'm intending to use that for total and the instantaneous for detailed usage information. This actually helped me diagnose my furnace heater strip failing late last year since I could see it was only drawing 10kW instead of 20kW which is what the coils are rated to. After running some continuity tests, it actually dropped down to 5kW. Turns out that when replaced, 3 of the 4 coils had failed.

The actual meter only has 4 digits and resets every few years. I have a spreadsheet that I've been using to manually record usage but that's only good for very high level trends since I only write it down at most daily and usually less often. For the last year I've been tinkering with this setup so I can rely on it enough to do that recording for me. It works ok overall but I'm still not confident since it's failed a few times.

Sorry, I misunderstood your original post. If the current power is less that 1.3kW to you get several zeros and then a tick, so the average is correct?

When I am on my PC I need to remind myself exactly what I have done with mine, I will post again later today.

If have refreshed my memory and in fact I do only record the instantaneous power. I then use the integral query to get the total usage over a period. So for example the grafana query to show the accumulated usage over the chart period is
SELECT cumulative_sum(integral("power"))/3600000 FROM "the_measurement" WHERE $timeFilter GROUP BY time($__interval) fill(null)

The reason for the cumulative sum as well as the integral is that the integral integrates it over the period of each time interval, and the sum adds those up across the chart. If you just want a single total over a period then you don't need the group by or the sum. The /3600000 is just to get the scaling right for my data.

Whether you can do this depends on the previous question I asked, when the consumption is low to you then get an occasional tick that you can use to get the actual power. What meter is it?

I am able to get my data to have a similar trend in Graphana when comparing the kWh increment and integral(kW) curves. They don't overlay which means I need to add some kind of math based on the period once I figure that out. I assume it will be something related to the reporting interval since the reader reports every ~30 seconds. In the plot, I've divided by 3600.

I'll look into this a little more since it looks promising.

The other thing that I probably need to do if I go this route is to filter off the floor since otherwise I'll be accruing 1.3kW when nothing is being used (worst case). I think it would be more accurate to simply clip that value so I under predict usage.

I'm using a Blueline power meter reader. There are 2 variants that are identical just with a different manufacturer printed on the shell.

  • Blueline BLI-28000
  • Black and Decker EM100B

The reader works very well and is universal to any power meter with a spinning wheel or some kind of flashing LED. Like I said initially, I'm not yet sold on long term durability. This is the same type of device someone could build really cheap with an arduino/ESP and a IR LED and detector. The primary advantage to this one is that it has a "weatherproof" shell with a well designed mounting system and becomes a near plug-and-play installation. If it fails again, I'm going to use one of these shells to enclose an ESP solution.

Here's the code including documentation for rtl_433 in case anyone is interested.

What exactly do you see if you are consuming less than 1.3kW?

The meter watches for when the spinning wheel comes around and records the "gap" (time for a revolution). When there is high load, it can take a few seconds to spin so the gap could be maybe 10s. The meter sends the last gap it recorded on it's regular transmit interval around 30s. So if the wheel is spinning every 10s it will only tell me about the last one even if the previous ones were spinning at 1s or 60s. The other value it sends is the accumulated "impulses". Each time the wheel spins, the meter keeps track so it doesn't lose any total usage but it likely loses maximum usage unless it's sustained across the transmit window.

The gap is converted to a kW in NR using the conversion provided which is:


where Kh is a constant written on the face of the meter (typically). In my case, Kh=24.

The meter has a maximum value for gap of 65533 (0xFFFD). gap is in ms so this is just over 65s. If the meter sees low power usage such that the wheel takes longer than 65s to spin, it will default to that value meaning the minimum reportable usage is:


If I'm using less than that, it simply records 1.3kW as the floor. However, if I use less than 1.3kW the impulses value is still correct since it sees every rotation and counts it. I was planning on using both gap and impulses for this reason.

gap converted to kW:

impulses converted to kWh. Top is running total. Bottom is incremental.

I don't care what node-red does at the moment. If you leave it consuming less that 1.3kW for some time, what do you see coming out of the the meter.

Do you mean the physical meter or the reader (B&D EM100B)? The data coming into NR from MQTT shows gap size. I don't save gap directly so I'll grab the data from MQTT and post after the furnace turns off for a while this morning.

So what does it show if the power is less than 1.3kW, for some time? That is a number of minutes, such that the wheel completes a couple of turns.
Also what happens to the total ticks over this time?

The gap reports 65533 if it takes too long and times out resulting in a calculated usage of 1.3kW. The impulses reports every ~3 minutes and correctly captures the total wheel spins. I have performed a derivative on kWh by 1h and overlaid it on the gap based kW usage. You can see that the trend is correct but terribly low fidelity. I hovered over a spot where no power was being used to show that the impulses based approach shows 0kW while the gap based is at 1.3kW.

Does it repeatedly do that or do you get one non-max each time the wheel completes a cycle? That is what I have been trying to determine for the last several posts.

If the wheel doesn't show up for 65533ms then the meter reports that at its next interval. If the wheel still has not shown up, it again reports 65533. It will continue reporting that until it sees a gap of less than 65533. So overnight when the furnace, water heater, etc are off and it's just small loads (laptop and phone chargers primarily) then I see a long stretch of 1.3kW.

My thermostat drops from 68 to 65F at night starting at 10:30PM iirc. It tries to get the temperature back up to 68 by 5AM so it turns the furnace off at 10:30 and on around 4. Between these times the furnace (highest load) and water heater (2nd highest load) are not on. The small ticks overnight show that some power is being used but it's below the threshold that the gap can detect but the impulses is still collecting it.

So you could add up the 65536 values until you get a non-65536 values and use that sum to determine the usage over that period. But even that is not too clever, because if the power suddenly steps up such that it turns several turns then it shows (I think you said) the last interval in that 65 seconds, which isn't what you want.
You could use the accumulated total instead, but because that does not report as often you will get errors.
It would be much easier if the device just provided a pulse each time round, which you could feed into an ESP device and send each pulse to NR immediately. The device tries to be clever, but doesn't really give what you want.

Or you could do what I have done which is use a PZEM 016 100A monitor with PZCT-02 current clamp, connect it to a Pi Zero and get accurate power readings up to once every 1.3 seconds (or less frequent if you don't need it that often).

Cost of the monitor and clamp was £15.48

Current clamps were something I considered originally. I read that there were issues with getting them configured for accuracy and they could drift though I don't really know what that entails. I haven't looked at them much other than periodically seeing TED or similar advertised. I have 2 panels so I'd have to get 4x 200A split clamps and figure out some way to connect them to a single controller but that's not impossible. I was going the current route because it seemed like a more fool proof approach since it reports exactly what is used and what the power company charges...except that it doesn't when the power usage is low.

The other thing I could do if this fails again is to use the housings that I have kept from the failed units and build my own ESP device that reports each time the wheel spins at that moment in time. Since it would have to be watching at all times, it would take too much power to be battery powered though, so I'd have to find a way to power it while remaining water tight. I have seen where people combined a 555 timer and ESP to do this but I'm pretty sure it would still require replacing batteries weekly or monthly. If I could get that to annually, I'd give that a try.

I'm trying to make the current solution work but I do see other methods that are probably superior such as the clamps or DIY rolled IR led approach.

In that case I think the best you can do is probably to add up the 65536 (is it actually 65536, or 65535?) values until you get a non-65536 and use that total as the time between pulses.

I haven't checked to see what happens when multiple 65533 are reported followed by a smaller value. That would be a good idea to determine if your suggestion would capture the minimum usage. Would be really nice if it did for sure!

Since I have the impulses value I should be able to determine what the length of time should be since I have the time (within 3 minutes) when the wheel spins. So at night I think I should be able to say if your suggestion works, each increment on kWh is some time apart and I should be able to add the gaps together to get there. Obviously that won't work if the value is 65533 for hours and then all of a sudden the furnace kicks on and spins the wheel 20 times in a minute.

I've added gap and impulses to my output to the database for now. I can use that to determine if I can glom the 65533 and the next real reading together to mean anything. My guess is no, but it's an interesting thought exercise either way!