Issue with derivative as replacement of DSMR gas usage per hour

sldewit commented 3 years ago

The problem

As I read in the pre-release notes in the next release of HassIO the DSMR gas per hour reading will be removed. This could be replaced with a derivative function. I tried that with below configuration (In YAML upload)

And this seems to work, however I also trend this new sensor using InfluxDB, but this seems to give a difference with a factor 10:

Am I doing something wrong?

What is version of Home Assistant Core has the issue?

core-2021.6.6

What was the last working version of Home Assistant Core?

No response

What type of installation are you running?

Home Assistant OS

Integration causing the issue

Derivative DSMR

Link to integration documentation on our website

https://www.home-assistant.io/integrations/derivative/ https://www.home-assistant.io/integrations/dsmr/

Example YAML snippet

- platform: derivative
    name: hourly_gas_consumption_2
    source: sensor.gas_consumption       
    unit_time: h

Anything in the logs that might be useful for us?

No response

Additional information

No response

probot-home-assistant[bot] commented 3 years ago

derivative documentation derivative source _{^{(message by IssueLinks)}}

probot-home-assistant[bot] commented 3 years ago

Hey there @afaucogney, mind taking a look at this issue as its been labeled with an integration (derivative) you are listed as a codeowner for? Thanks! _{^{(message by CodeOwnersMention)}}

frenck commented 3 years ago

Seems like there is no time_window set? In that case, the derivative will only take the last two readings into account, thus no guarantee it is an window of an hour.

sldewit commented 3 years ago

@frenck I did that before, but then I got completely different values in my trending. But if that is how it should be setup then I will try that for a while.

frenck commented 3 years ago

But if that is how it should be setup

There is no "how it should", as this is free to configure to your own likings and wishes.

sldewit commented 3 years ago

What I want to do is have the same info as I used to get from the DSMR tooling. So if that requires the time_window to be set to "01:00:00" then I will try that.

sldewit commented 3 years ago

@frenck I just tried this and this also seems to give other values. What does the original DSMR derivative function do? Over what time period does that make a derivative? Like I said, I want to make the same function as will be depraved in the DSMR integration.

frenck commented 3 years ago

You can view the removed sensor here: https://github.com/home-assistant/core/pull/52147

sldewit commented 3 years ago

You can view the removed sensor here: #52147

The way I read the code of the removed sensor there is that it simply creates a derivative based on the current and last update. And then I don't get why the regular derivative without time_window doesn't show the same value. But reading Python code is not my best skill I must admit.

sldewit commented 3 years ago

@frenck and @afaucogney I just tried a couple of options and this is how it shows now:

  - platform: derivative
    name: hourly_gas_consumption_2 (**Nieuwe meting 2**)
    source: sensor.gas_consumption       
    unit_time: h
  - platform: derivative
    name: hourly_gas_consumption_3 (**Nieuwe meting 3**)
    source: sensor.gas_consumption       
    unit_time: h
    time_window: "01:00:00"

Nothing seems to do the same as the original sensor from DSMR does. Maybe you two can check what the difference in implementation is. At this moment the calculation number 2 follows the same profile in the graph. Only it is a factor 10 higher. And I cannot really understand that yet. Maybe I'm doing something wrong. But I think it is good for the current users of the DSMR integration to check what the correct form fit replacement would be for the depraved functionality.

frenck commented 3 years ago

If anything, i would consider the old implementation in DSMR the weird one. It has been removed as we don't allow for integrations to provide virtual sensors.

The question should not be: which looks the same. The question is: which one is correct?

pimw1 commented 3 years ago

That's the right question indeed. I did some quick testing with the current implementation:

I've calculated the area under the curve (AUC):

Total gas usage is 0.087 m3 when using the calculated "gas usage per hour".

When comparing this to the actual value from DSMR: The real amount is 0.087, an exact match.

I think it is relevant to do the same with the derivative platform and compare the area under the curve (AUC). My expectation: the AUC will be exactly the same, but the hight and width of the curve will be different. That would make a lot of sense, since the Gas meter can only give a value once every couple of minutes (that is baked in the hardware of the physical Dutch gas meter itself), so every derivative setting will be suboptimal anyway (as compared to the "truth", which we simply cannot observe every milisecond).

pimw1 commented 3 years ago

As a comparison, see below the timestamps of my water usage of the same period:

As you can see, the real event started around 11:51, and ended at 11:58. Right in the 11:00-12:00 time window. This also supports my statement above: the gas meter can only show a value once every few minutes, because the gas meter is not precise enough to track every cm3 of gas usage. The gas meter will only trigger after more gas has been used.

pimw1 commented 3 years ago

I've implemented some test derivate sensors, so we can start comparing. I don't store DSMR sensor data in Home Assistant at all (it would flood the database), the values are stored directly in a LTSS database. Therefore, i cannot recreate the above event with the below derivate sensors. The only thing i can do, is to create a new event (turning on the hot water tap for a while), and start comparing. Stay tuned.

I hope to have some insights before the next release tomorrow. In this way, a potential guideline for DSMR derivate can be added in the changelog (i can tune in in the youtube release party as well).

`

platform: derivative name: gas_consumption_1m source: sensor.gas_consumption unit_time: h time_window: "00:01:00"
platform: derivative name: gas_consumption_2m source: sensor.gas_consumption unit_time: h time_window: "00:02:00"
platform: derivative name: gas_consumption_5m source: sensor.gas_consumption unit_time: h time_window: "00:05:00"
platform: derivative name: gas_consumption_10m source: sensor.gas_consumption unit_time: h time_window: "00:10:00"
platform: derivative name: gas_consumption_15m source: sensor.gas_consumption unit_time: h time_window: "00:15:00"
platform: derivative name: gas_consumption_30m source: sensor.gas_consumption unit_time: h time_window: "00:30:00"
platform: derivative name: gas_consumption_60m source: sensor.gas_consumption unit_time: h time_window: "01:00:00"
platform: derivative name: gas_consumption_undefined source: sensor.gas_consumption unit_time: h `

sldewit commented 3 years ago

I just tried to calculate the same with my info from InfluxDB:

This is the calculation I did: <html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">

Start | Eind | Duur | Value | Area under the curve -- | -- | -- | -- | -- 13:35:05 | 13:36:30 | 00:01:25 | 1,44 | 0,001416667 13:35:25 | 13:40:50 | 00:05:25 | 0,146 | 0,00054919

Top values are from the yellow line.

Values of this calculation is quite different. (By the way, this is done using the same as your "gas_consumption_undefined". Thanks for debugging this issue by the way!

sldewit commented 3 years ago

Trend of actual meter values as indicated by DSMR:

This means in this event this was the usage:

First reading: 2847,1020 m3 Second reading: 2847,1140 m3 Usage would be 0.012 m3 in this event

pimw1 commented 3 years ago

Alright, i did the analysis. My statement seems to be correct: the area under the curve is stable for different settings.

The scenario: Hot water tap on between 13:44:00 and 13:49:00. Real gas consumption: 0.148 m3

Original DSMR gas_consumption value: perfect match (0.148)

Derivative gas_consumption_1m value: perfect match (0.150, since i did a visual check, some deviations are there because of human error)

Derivative gas_consumption_2m value: perfect match (0.153, since i did a visual check, some deviations are there because of human error)

Derivative gas_consumption_5m value: perfect match (0.150, since i did a visual check, some deviations are there because of human error)

Derivative gas_consumption_10m value: perfect match (0.149, since i did a visual check, some deviations are there because of human error)

Derivative gas_consumption_15m value: perfect match (0.153, since i did a visual check, some deviations are there because of human error)

Derivative gas_consumption_30m value: not perfect match (0.127, since i did a visual check, some deviations are there because of human error, but this might be more than just the human error)

Derivative gas_consumption_60m value: no match (0.429, i think this might be too much deviation to be accounted for by human error, but i could be wrong).

Derivative gas_consumption_undefined value: not perfect match (0.128, since i did a visual check, some deviations are there because of human error, but i could be wrong)

These results do make sense: the longer the time window, the wider and lower the curve is, but the area under the curve remains stable. The execption being the very long time window of 1 hour (which is not making sense anyway, because you want to see the current usage, and not some average of a whole hour).

My advice: take the 5 minute derivative:

it resembles the original dsmr value very closely
the original dsmr value is making sense (area under the curve is matching)
no long overshoots
no weird gaps except a 6 second gap somehwere between 13:49:21 and 13:49:27. You can compensate for this in two ways: First method: make a timegroup alias in Grafana of 1 minute: $__timeGroupAlias("time",1m). Second method: extend the derivate somewhat, for example to 5:30 minutes or 6:00 minutes?

Anyhow, this is my proposed solution:

Configuration.yaml:

`

platform: derivative name: gas_consumption_5m source: sensor.gas_consumption unit_time: h time_window: "00:05:00" `

Grafana: SELECT $__timeGroupAlias("time",1m), entity_id AS metric, max(cast(state as numeric)) as value FROM ltss WHERE $__timeFilter("time") AND entity_id in ('sensor.gas_consumption_5m') GROUP BY 1,2 ORDER BY 1,2

End-result: Original DSMR:

Proposed solution:

pimw1 commented 3 years ago

The last proof of concept: another hot water tap between 15:52:00 and 15:57:00.

Original DSMR:

Derivate with 5 minute time window:

Derivate with 5m30s time window:

Derivate with 6 minute time window:

Derivate with 1 minute time window: no good solution, sensor data is simply coming in too slow for a small window like 1 minute (even though the AUC is the same):

Derivate with 10 minute time window: no good solution, too much overshoot in the graphs (even though the AUC is the same):

The derivative with a 5 minute interval resembles reality the best. And is the best comparison with the DSMR solution (which makes perfect sense: when the DSMR implemention was developped, the creator has most likely done similar testing as me).

@frenck : could you please add in the release notes that a good replacement is a derivative with the following settings? source: sensor.gas_consumption unit_time: h time_window: "00:05:00"

pimw1 commented 3 years ago

I just tried to calculate the same with my info from InfluxDB:

This is the calculation I did:

Start Eind Duur Value Area under the curve 13:35:05 13:36:30 00:01:25 1,44 0,001416667 13:35:25 13:40:50 00:05:25 0,146 0,00054919 Top values are from the yellow line.

Values of this calculation is quite different. (By the way, this is done using the same as your "gas_consumption_undefined". Thanks for debugging this issue by the way!

I think you are making some errors in the calculation: you are looking for the current gas usage, expressed in m3/hour. Therefore, the calculation should be like this:

duration in minutes * ( value / 60 minutes )

In your yellow case: 1.417 (= 1 minute 25 seconds) * (1.44 /60) = 0.034 m3 of total gas usage.

I am not sure about your other graphs, but something seems to be off in your graphs. If you like, we can do screen sharing via Teams or Discord so i can look together with you.

sldewit commented 3 years ago

Just changed the configuration to the 5 minutes you indicated. And the graphs look a lot better now:

I will monitor this solution for a day and see if it matches better. Only difference is that this graph goes back to 0 after every measurement.

pimw1 commented 3 years ago

I will monitor this solution for a day and see if it matches better. Only difference is that this graph goes back to 0 after every measurement.

Good to hear it is working for you. Regarding the zeros: try something similar as the "timegroup alias in Grafana": what you see, is purely how influxdb is summarizing and visualizing the data.

edit: to clarify this statement: reality is that a gas usage measurement is only send once every x minutes. You could consider this as a shortcomming of the Dutch gas meter. Every visualization software is handling this differently, and their are lots of different methods to handle this nasty situation. The timegroup in Grafana is doing this for me.

Another solution is to increase the time window to 5m:30s or 6 minutes, but the graphs will look slightly different as compared to the original of DSMR. That brings us back to the valid remark of @frenck: which one is correct? Both. The area under the curve will be the same.

ghost commented 3 years ago

I'd like to know the final solution too. Since i'd like to know when and how hard my gas is running, like it did before :-)

sensor:
  - platform: derivative
    name: hourly_gas_consumption
    source: sensor.gas_consumption       
    unit_time: h
    time_window: "00:05:00"

?

pimw1 commented 3 years ago

If your goal is to have a similar output as the depricated DSMR gas_consumption, than a 5 minutes time window is indeed the best. But it comes down to personal preference.

Kertz1954 commented 3 years ago

Hi, I have also tried this Derivative platform to retrieve an hourly gas usage. I setup 2 platforms:-

platform: derivative source: sensor.gas_consumption_daily_total name: DSMR Hourly Gas Use unit_time: h unit: m3/h platform: derivative name: hourly_gas_consumption_1 source: sensor.gas_consumption unit_time: h time_window: "01:00:00" unit: m3/h Both gave results conflicting results, so I created a numeric helper for hourly usage and update this every hour with my daily usage (another numeric helper updated at midnight). I now have the correct hourly usage.

pimw1 commented 3 years ago

Based on my analysis, a derivate time window of "01:00:00" is the worst setting to choose. My advice is to set it way shorter.

However, in your first sentence, you are mentioning "Hi, I have also tried this Derivative platform to retrieve an hourly gas usage.". If your goal is to have the hourly gas usage, you are looking for a cumulative number. That is completely something else as compared to the current gas usage expressed in m3/hour. For a cumulative hourly gas usage, you can do the following in the Configuration.yaml (old method, per 2021.7 release this is done via the GUI, but i have not installed 2021.7 yet, so i cannot tell you how exactly):

utility_meter:
  hourly_gas:
    source: sensor.gas_consumption
    cycle: hourly

Kertz1954 commented 3 years ago

Thanks pimw1 for the update. I have since changed the window to 00:05:00 for both platforms and the expected results are as you have explained. However, as I am looking to see an hourly total, I will stick with my method of just using an additional numeric helper.

erkr commented 3 years ago

Hi. Also tried to use the derivative platform to create a hourly gas consumption sensor. But it doesn't work when there are no new values (no gas consumption). Then the sensor continues to present the last derivative value, in stead of 0. I would expect that it becomes 0 if no updates are received, longer then the time window specified. Background info: My DSMR values are received via MQTT. It seems these sensors only trigger a calculation when a different value is received.

pimw1 commented 3 years ago

Hi erkr,

I've double checked this in my database. For me, it is always going back to zero (see an example below). I don't know why that is different for you. Could you copy-paste your exact configuration.yaml derivative code?

Anyhow, if you are unable to fix this with the derivative, you can also do this in your visualization software. For example, in Grafana you can add the following in the "group by" statement of the SQL code: $__timeGroup("time",'10m', 0) This will enforce Grafana to visualize zero's instead of the last value.

erkr commented 3 years ago

Hi Pimw My assumption is that the derivative sensor only gets triggered for a new calculation when the source sensor is updated. Problem with that is that many sensors (like my MQTT) are optimized to only record changes. As a result it never will calculate 0! This is my configuration:

sensor:
  - platform: mqtt
    name: Total Gas Usage
    icon: mdi:fire
    unit_of_measurement: 'm3'
    state_topic: "sensors/power/p1meter/gas_meter_m3"
    value_template: "{{ value|float / 1000 }}"
  - platform: derivative
    source: sensor.total_gas_usage
    name: gasverbruik 
    unit_time: d
    time_window: "00:05:00"

Best regards Eric

pimw1 commented 3 years ago

My configuration.yaml is slighly different, i'm using unit_time 'h' so i get a calculated value of current gas usage per hour. Maybe that will solve it for you?

  - platform: derivative
    name: gas_consumption_5m
    source: sensor.gas_consumption
    unit_time: h
    time_window: "00:05:00"
    unit: m3/uur
    round: 3

erkr commented 3 years ago

Hi Pimw,

You're settings don't behave differently. Key is that my sensor.total_gas_usage doesn't get updates as long there is no gas usage (marked red): Capture2 Could you check your sensor.gas_consumption history to check if it updates even when the value didn't change?!

Here is the result: Capture As you can see in the second picture, the top chart is the sensor.total_gas_usage, the bottom chart is the derived one. The green line marks the moment in time where there was a short gas usage. The derived sensor changes from zero to 0.8.. That is good, BUT after that the derived sensor doesn't go back to zero any more, marked by the red circle. this stays the same until there is a new increase in gas consumption

pimw1 commented 3 years ago

Hi Eric,

Very good analysis!

Could you check your sensor.gas_consumption history to check if it updates even when the value didn't change?!

Your hypothesis is correct: my gas consumption sensor is updating every 5 seconds, even when nothing has changed.

erkr commented 3 years ago

Thanks for your confirmation! I guess a feature request for the derivative sensors to return to zero if no new updates are received. Could use the time_window parameter for that as well.

pimw1 commented 3 years ago

Happy to help.

Perhaps this is a solution? https://community.home-assistant.io/t/update-record-of-sensor-template-every-5-min/37463/4 As per my understanding, this should update the value in a fixed interval time, thereby propagating the last known value.

erkr commented 3 years ago

Hi Pimw1 Thx for your help! I fixed it even simpler, by adding the command force_update: true to the MQTT sensor. Now I get updates, even when the value didn't change, and then the derivative works as well

pimw1 commented 3 years ago

Awesome! I'm not very experienced with MQTT, i'll remember your trick.

pimw1 commented 3 years ago

@sldewit : is your issue solved? If yes, could you close the issue please?

home-assistant / core