Offset sensor by an hour

Configuration
1

Hi all,
I’ve got a gas consumption sensor that retrieves it data hourly. However, due to the time it retrieves it (1 after the hour, so 9:01, 10:01) my energy panel doesn’t work correctly because it registers gas usage the hour after it was actually used.

Example:
I take a shower from 8:15 - 8:25, the gas usage from that shower gets sent to HA at 09:01, registering the usage from to 09:00 - 10:00 instead of between 08:00 - 09:00.

I quite like the energy panel and can’t change the moment the data is retrieved, so I’d like to create a sensor that takes the gas_consumption sensor, which is total gas usage since ever, and offset the last updated by one hour to make sure it uses the correct hour in the energy panel.

Can anyone help me with this? I haven’t gotten it to work unfortunately.

2

What is the sensor?

3

@nickrout
The sensor is currently called sensor.gas_meter_gas_consumption, coming from the DSMR. It logs the entire gas usage read from my meter since day one (so right now it’s at ~7k m3).

as seen in the screenshot below, it logs it every minute after the hour, therefore providing the Energy Panel with the wrong data.
image

4

So presumably using this DSMR Component — ESPHome?

The docs say

This component is passive, it does not transmit any data to your equipment, the equipment always transmits data which this component decodes and updates the configured sensors at the pace the data is received.

So the solution appears to lie in setting the meter to report more often.

Assuming that isn’t possible, you may be able to manipulate the time of the sensor reading using a SQL command via this integration SQL - Home Assistant

5

@nickrout
Unfortunately I can’t get the UPDATE statement to work in the sql sensor, as this does expect an output and only allows select statements. I have to run this every hour. And letting the meter report more often is not an option :(.

6

To whom it may concern in the future;
I added a trigger directly to the DB with the Sqlite for Web addon, with the solution below fixing my energy panel. It backdates the start-time for my gasmeter by an hour.

I first added a WasUpdated column (boolean) so I keep track of the ones that have been changed.

CREATE TRIGGER trg_gasmeter_Datetimefix AFTER INSERT ON statistics
BEGIN
  UPDATE statistics 
  SET start_ts = new.start_ts - 3600, 

  wasupdated = 1
  
  WHERE id = NEW.id 
  and metadata_id = ( 
                      select id 
                      from statistics_meta 
                      where statistic_id = 'sensor.gas_meter_gas_consumption' 
                      ) 
  and wasupdated is null
;
END

Note: Your sensor name may vary from mine so change that in the SQL-statement. The metadata ID doesn’t seem to change, so you could hardcode it. I prefer not to.

1 Like
7

Thank you for this solution.

Do you know if this also shifts the cost calculations?

8

Super solution!
Afterwards I ran an UPDATE query to also update historic data :slight_smile:
(be sure to update your sensor name (sensor.gas_meter_gas_consumption)

UPDATE statistics 
  SET start_ts = start_ts - 3600,   wasupdated = 1
  
  WHERE wasupdated is null
  and metadata_id = ( 
                      select id 
                      from statistics_meta 
                      where statistic_id = 'sensor.gas_meter_gas_consumption' 
                      )