Peak / Off-Peak energy consumption based on existing smart plug data

Configuration
1

Hi all

I’m trying to set up a report that would tell me, over time, how much electricity my tumble dryer (or any other device) has used split in Peak and Off Peak aggregates. This is to improve energy efficiency and show people in my house best practicies. To be clear, the goal would be to have a way to display how much power (either absolutely or in percentage) has been consumed in Peak vs Off-Peak hours by time interval (week on week, month on month, etc).

The appliance is hooked up to a smart plug that provides “instant” power reading, on top of total KWh used. I have downloaded a CSV report and used Excel formulas to create that chart but I’d really like to do it all in Home Assistant if at all possible.

My logic / progress so far:

  1. Get an hourly reading of the total energy consumption from historical stats (CSV).
  2. Calculate a delta of power usage per 1 hour slot (approximately shows power used in that hour).
  3. Aggregate those deltas based on Peak and Off-Peak time intervals and put the values in a stacked columns chart.

image

If something like this was possible in HA alone, I think it would be very useful especially for being able to just use existing stats data, and if the approach could be made into a card I would gladly look into developing something (never done HA specific but I’m familiar with JS, SQL and programming).

Alternatively, I’ve read a lot about setting up energy dashboards, helpers, etc, and I must admit I’m a bit lost in the thick of it all, as most forum discussions and articles I’ve found on dual tariffs are set up for total energy, while this would be a device specific approach. I might have just not used the right language for my searches.

Thank you!

2

That is far too labour intensive and should be able to be completely automated.

First thing is you need to measure the peak and off-peak energy use separately. This is easy with Utility Meter Tariffs and an automation. See: How to use Utility Meter Tariffs to conditionally measure things

For graphing this in HA, ApexCharts would probably be your best bet. Though the Sankey chart may also be another option.

3

Hi, thank you for your reply!

I’ve followed your guide and set up daily peak and off peak energy meter sensors, they now make sense to me and are really helpful.

I’ve created an automation based on the Octopus Energy integration for switching between the two tariffs, which makes the setup easier and should take care of timezone and futureproof (provided the integration is kept up to date).

Automation code 
service: select.select_option
target:
  entity_id:
    - select.washing_machine_energy_meter
    - select.tumble_dryer_energy_meter
data:
  option: >-
    {{ 'offpeak' if
    is_state('binary_sensor.octopus_energy_electricity_22l4343257_1900027207449_off_peak',
    'on') else 'peak' }}

I also tried an alternative setup with timed switching based on the Time integration, as the hours are based on UTC time, as per here).

With the ApexCharts card I managed to get to here:

image

Chart card code 
type: custom:apexcharts-card
apex_config:
  legend:
    show: true
  chart:
    stacked: true
  dataLabels:
    background:
      borderWidth: 0
      opacity: 0
      foreColor: '#000'
  plotOptions:
    bar:
      borderRadius: 5
      dataLabels:
        position: center
graph_span: 7d
span:
  end: day
header:
  show: true
  title: Washing machine energy usage 7d
series:
  - entity: sensor.washing_machine_energy_meter_offpeak
    color: '#77dd77'
    show:
      extremas: false
      header_color_threshold: true
      datalabels: true
    type: column
    name: Off-Peak
    group_by:
      func: last
      duration: 1d
  - entity: sensor.washing_machine_energy_meter_peak
    color: '#ff6961'
    show:
      datalabels: true
      header_color_threshold: false
      extremas: false
    type: column
    name: Peak
    group_by:
      func: last
      duration: 1d

Which is already a massive improvement! The bit I’m struggling with is how to aggregate the last value of each 24 hours period over time. I’ve read other posts where users have set up more helper sensors (weekly, monthly, etc) but that seems extremely inefficient as it would mean setting up dozens and dozens of sensors (for each appliance) and also (if I understand how these work) not being able to have sliding time windows (eg last 7 days).

4

Just change this in the chart::

5

No sure I explained what I’m trying to do very clearly… The current stacked columns chart shows daily split of peak/offpeak usage (my original use case). I can also use the daily sensor in any card to show today’s values.

What I’m wondering is, can I “easily” aggregate these daily values (which need to be the end of day ones) over time, eg over 7 days, to show in a card the absolute values over that period of time, or in a pie chart the split of peak/offpeak over the last 7 days?

Changing the duration to 7d makes the columns disappear, and if I understand the doc corretly that would mean “last value in the 7d period” rather than sum of daily last values.

6

Quick update: I’ve tried a few approaces with templated sensors with triggered daily resets (I was hoping to have 1 value per day to aggregate), but for some reason these approaches don’t sit well with my brain.

While I figure it out, I’ve implemented a couple of SQL sensors to extract exactly the values I’m after from the daily cycled energy meter sensors. Here’s the result when used with an ApexChart pie:

image

This takes the data from a SQL sensor using this query to get the MAX daily value from the energy meter sensor:

SQL query 
WITH daily_max AS (
	SELECT
		MAX(state) AS MaxState
	FROM
		states s
		JOIN states_meta sm
			ON sm.metadata_id = s.metadata_id
	WHERE
		sm.entity_id = 'sensor.tumble_dryer_energy_meter_offpeak'
		AND DATE(last_reported_ts, 'unixepoch') >= (DATETIME('now','-7 days'))
	GROUP BY
		DATE(last_reported_ts, 'unixepoch')
)
SELECT
	SUM(MaxState) AS state
FROM
	daily_last_timestamp
SQL sensor configuration screenshot

image
image182×964 22.3 KB

I still think I need a different approach as ultimately I’d like to be able to aggregate data across weeks and months (eg show the peak/offpeak usage trends) and the SQL sensor doesn’t seem to be able to extract more than a single value. I’ll keep trying.