Change old stats to observe Peak / Offpeak

I’ve had the Sense energy monitor for the last 2 years or so, but I’ve never previously setup Home Assistant to keep track of peak vs offpeak energy usage. I just set this up so that it will now break out the peak vs offpeak usage (which in my case is 2-7pm during only June-Sept). While I’ll be fine going forward with the sensors, I want to figure out how to deal with historic data.

Sense stores its daily usage data in sensor.daily_usage where it will post the past hour’s result to the sensor and then reset every day at midnight. Up until now, this was fine for energy dashboards.

But now that I have peak/offpeak tracked, I’m going to be using the sensor of sensor.daily_usage_peak and sensor.daily_usage_offpeak in the energy dashboard. If I simply remove the old sensor of sensor.daily_usage from my dashboard tracking, that will remove all historical data. It’s almost like I want the energy dashboard to evaluate the data in sensor.daily_usage until May 31, and then June 1 onward, I want it to use sensor.daily_usage_peak and sensor.daily_usage_offpeak. If I leave all three sensors in the dashboard, then my energy usage will effectively show double usage from the time I created the peak/offpeak sensors.

Other than messing with the database (i’d have to figure out implications of doing this), or leveraging the recorder.import_statistics of the spook tools, are there any other ideas I am not thinking of?

Oof, this was painful to do. Hopefully this helps someone else that may stumble across this in the future. This worked for me on HA v 2023.6.1.

You’ll have to mess around with your database directly, so a backup is recommended. Also recommended to try this on a test system first before trying this on your main homeassistant instance.

  1. Delete all the stats for the metadata_id in statistics and statistics_short_term in the database
  2. Data mine your old data, and then data mine some more. You can download this data from home.sense.com and use excel to filter out the total usage data. Split this out into peak data and offpeak data. You’ll need a cumulative sum of your readings, and you’ll have to prepare that for the import.
  3. Import the data using the Spook recorder.import_statistics service. Use a syntax like the following. You’ll likely want one for offpeak and one for peak, depending on your use case
service: recorder.import_statistics
data:
  has_mean: false
  has_sum: true
  statistic_id: sensor.daily_power_offpeak
  source: recorder
  name: "NULL"
  unit_of_measurement: kWh
  stats:
    - start: 2021-06-25T19:00-04:00
      sum: 3.826
    - start: 2021-06-25T20:00-04:00
      sum: 7.16
    - start: 2021-06-25T21:00-04:00
      sum: 9.847
    - start: 2021-06-25T22:00-04:00
      sum: 11.556
  1. Immediately after importing, take note to your final row, and what the cumulative sum was.
  2. Modify your database, and run a query that sets the sum of the statistics_short_term for the metadata_id to the cummulative sum that you noted above. If you don’t do this, then on the next run, your graphs will be all out of whack and will be very challenging to find the data to be able to correct it. You’ll only need to do this to the first record of statistics_short_term, as anything afterwards, the db will add onto the sum you listed here.
    **side note: last_reset_ts should have worked here so you didn’t have to manually set the sum, but it did not for some reason. Unsure if this is a bug, or perhaps it is me not understanding how this field is handled in the statistics.
  3. Profit, and hope to never have to do this again.