Logic to minimize the tariff costs in Home Assistant

configuration.yaml

# Sensor data for Home assistant to handle EON upcomping effect tariffs that will go live 2026-11-01 in SE4 in Sweden
# According to my knowledge the tariff will only occur between 1 november to 31 March and only Monday to friday between 7-19
# Grid Energy Peak Hourly Monthly Tariff:   Will be 0 if there is no data for the sensor.
# Grid Import Limit:                        Will be used in my Ferroamp system to do peak shaving with the higest hour peak of the month.
#                                           When no tariff cost are active then use all my availabe effect (11kW / 16A fuse).                                            
# Grid Energy Peak Hourly Monthly:          Just to see the higest hour effect peak of the month.
# Grid Energy Peak Hourly Daily Tariff:     Daily hour energy peak during tariff prices.
# Grid Energy Peak Hourly Daily:            Just to see the higest hour eccect peak of the day.
# Tariff Mode:                              Binary sensor to use tariff mode or not.

# You need to have a utility meter for hourly energy usage from the grid, ie sensor.meter_energy_consumed_hourly in my case.

binary_sensor:
  - platform: template
    sensors:
      bs_tariff_mode:
        friendly_name: Tariff Mode
        value_template: >-
          {% set now = now() %}
          {% set month = now.month %}
          {% set hour = now.hour %}
          {% set weekday = now.weekday() %}
          {{ weekday < 5 and hour >= 7 and hour < 19 and (month >= 11 or month <= 3) }}
        unique_id: bs_tariff_mode

sql:
  # Sensor for peak hourly consumption (07:00-19:00) current month - between Monday to Friday the 1/11 until 31/3 - using statistics and states table
  - name: "Grid Energy Peak Hourly Monthly Tariff"
    query: >
      SELECT COALESCE(MAX(val), 0) as peak_kwh
      FROM (
        -- Values from statistics table (aggregated historical values)
        SELECT CAST(s.state AS FLOAT) AS val
        FROM statistics s
        INNER JOIN statistics_meta sm ON s.metadata_id = sm.id
        WHERE sm.statistic_id = 'sensor.meter_energy_consumed_hourly'
          AND s.state IS NOT NULL
          -- Peak tariff hours: Mon-Fri 07:00-18:59
          AND CAST(strftime('%H', datetime(s.start_ts, 'unixepoch', 'localtime')) AS INTEGER) BETWEEN 7 AND 18
          AND CAST(strftime('%w', datetime(s.start_ts, 'unixepoch', 'localtime')) AS INTEGER) BETWEEN 1 AND 5
          -- Peak tariff months: Nov-Mar
          AND strftime('%m', datetime(s.start_ts, 'unixepoch', 'localtime')) IN ('11','12','01','02','03')
          AND strftime('%Y-%m', datetime(s.start_ts, 'unixepoch', 'localtime')) = strftime('%Y-%m', 'now', 'localtime')
        UNION ALL
        -- Values from states table (most recent raw states)
        SELECT CAST(s2.state AS FLOAT) AS val
        FROM states s2
        INNER JOIN states_meta sm2 ON s2.metadata_id = sm2.metadata_id
        WHERE sm2.entity_id = 'sensor.meter_energy_consumed_hourly'
          AND s2.state NOT IN ('unknown', 'unavailable', 'None', '')
          -- Peak tariff hours: Mon-Fri 07:00-18:59
          AND CAST(strftime('%H', datetime(ROUND(s2.last_updated_ts), 'unixepoch', 'localtime')) AS INTEGER) BETWEEN 7 AND 18
          AND CAST(strftime('%w', datetime(ROUND(s2.last_updated_ts), 'unixepoch', 'localtime')) AS INTEGER) BETWEEN 1 AND 5
          -- Peak tariff months: Nov-Mar
          AND strftime('%m', datetime(ROUND(s2.last_updated_ts), 'unixepoch', 'localtime')) IN ('11','12','01','02','03')
          AND strftime('%Y-%m', datetime(ROUND(s2.last_updated_ts), 'unixepoch', 'localtime')) = strftime('%Y-%m', 'now', 'localtime')
      )
    column: 'peak_kwh'
    unit_of_measurement: 'kWh'
    unique_id: grid_energy_peak_hourly_monthly_tariff

  # Sensor for grid import limit to be used to update Ferroamp Import Threshold - using statistics and states table
  - name: "Grid Import Limit"
    query: >
      SELECT COALESCE(MAX(val)*1000, 11000) as peak_wh
      FROM (
        -- Values from statistics table (aggregated historical values)
        SELECT CAST(s.state AS FLOAT) AS val
        FROM statistics s
        INNER JOIN statistics_meta sm ON s.metadata_id = sm.id
        WHERE sm.statistic_id = 'sensor.meter_energy_consumed_hourly'
          AND s.state IS NOT NULL
          -- Peak tariff hours: Mon-Fri 07:00-18:59
          AND CAST(strftime('%H', datetime(s.start_ts, 'unixepoch', 'localtime')) AS INTEGER) BETWEEN 7 AND 18
          AND CAST(strftime('%w', datetime(s.start_ts, 'unixepoch', 'localtime')) AS INTEGER) BETWEEN 1 AND 5
          -- Peak tariff months: Nov-Mar
          AND strftime('%m', datetime(s.start_ts, 'unixepoch', 'localtime')) IN ('11','12','01','02','03')
          AND strftime('%Y-%m', datetime(s.start_ts, 'unixepoch', 'localtime')) = strftime('%Y-%m', 'now', 'localtime')
        UNION ALL
        -- Values from states table (most recent raw states)
        SELECT CAST(s2.state AS FLOAT) AS val
        FROM states s2
        INNER JOIN states_meta sm2 ON s2.metadata_id = sm2.metadata_id
        WHERE sm2.entity_id = 'sensor.meter_energy_consumed_hourly'
          AND s2.state NOT IN ('unknown', 'unavailable', 'None', '')
          -- Peak tariff hours: Mon-Fri 07:00-18:59
          AND CAST(strftime('%H', datetime(ROUND(s2.last_updated_ts), 'unixepoch', 'localtime')) AS INTEGER) BETWEEN 7 AND 18
          AND CAST(strftime('%w', datetime(ROUND(s2.last_updated_ts), 'unixepoch', 'localtime')) AS INTEGER) BETWEEN 1 AND 5
          -- Peak tariff months: Nov-Mar
          AND strftime('%m', datetime(ROUND(s2.last_updated_ts), 'unixepoch', 'localtime')) IN ('11','12','01','02','03')
          AND strftime('%Y-%m', datetime(ROUND(s2.last_updated_ts), 'unixepoch', 'localtime')) = strftime('%Y-%m', 'now', 'localtime')
      )
    column: 'peak_wh'
    unit_of_measurement: 'Wh'
    unique_id: grid_import_limit

  # Sensor for peak hourly consumption Monthly - using statistics and states table
  - name: "Grid Energy Peak Hourly Monthly"
    query: >
      SELECT COALESCE(MAX(val), 0) as peak_kwh
      FROM (
        -- Values from statistics table (aggregated historical values)
        SELECT CAST(s.state AS FLOAT) AS val
        FROM statistics s
        INNER JOIN statistics_meta sm ON s.metadata_id = sm.id
        WHERE sm.statistic_id = 'sensor.meter_energy_consumed_hourly'
          AND s.state IS NOT NULL
          AND strftime('%Y-%m', datetime(s.start_ts, 'unixepoch', 'localtime')) = strftime('%Y-%m', 'now', 'localtime')
        UNION ALL
        -- Values from states table (most recent raw states)
        SELECT CAST(s2.state AS FLOAT) AS val
        FROM states s2
        INNER JOIN states_meta sm2 ON s2.metadata_id = sm2.metadata_id
        WHERE sm2.entity_id = 'sensor.meter_energy_consumed_hourly'
          AND s2.state NOT IN ('unknown', 'unavailable', 'None', '')
          AND strftime('%Y-%m', datetime(ROUND(s2.last_updated_ts), 'unixepoch', 'localtime')) = strftime('%Y-%m', 'now', 'localtime')
      )
    column: 'peak_kwh'
    unit_of_measurement: 'kWh'
    unique_id: grid_energy_peak_hourly_monthly

  # Sensor for peak hourly daily consumption (07:00-19:00) Monday to Friday the 1/11 until 31/3 - using states table
  - name: "Grid Energy Peak Hourly Daily Tariff"
    query: >
      SELECT COALESCE(MAX(CAST(state AS FLOAT)), 0) as peak_kwh
      FROM states s
      INNER JOIN states_meta sm ON s.metadata_id = sm.metadata_id
      WHERE sm.entity_id = 'sensor.meter_energy_consumed_hourly'
      AND state NOT IN ('unknown', 'unavailable', 'None', '')
      AND strftime('%Y-%m-%d', datetime(ROUND(s.last_updated_ts), 'unixepoch', 'localtime')) = strftime('%Y-%m-%d', 'now', 'localtime')
      AND CAST(strftime('%H', datetime(ROUND(s.last_updated_ts), 'unixepoch', 'localtime')) AS INTEGER) BETWEEN 7 AND 18
      AND CAST(strftime('%w', datetime(ROUND(s.last_updated_ts), 'unixepoch', 'localtime')) AS INTEGER) BETWEEN 1 AND 5
    column: 'peak_kwh'
    unit_of_measurement: 'kWh'
    unique_id: grid_energy_peak_hourly_daily_tariff

  # Sensor for peak hourly consumption - using states table
  - name: "Grid Energy Peak Hourly Daily"
    query: >
      SELECT COALESCE(MAX(CAST(state AS FLOAT)), 0) as peak_kwh
      FROM states s
      INNER JOIN states_meta sm ON s.metadata_id = sm.metadata_id
      WHERE sm.entity_id = 'sensor.meter_energy_consumed_hourly'
      AND state NOT IN ('unknown', 'unavailable', 'None', '')
      AND strftime('%Y-%m-%d', datetime(ROUND(s.last_updated_ts), 'unixepoch', 'localtime')) = strftime('%Y-%m-%d', 'now', 'localtime')
    column: 'peak_kwh'
    unit_of_measurement: 'kWh'
    unique_id: grid_energy_peak_hourly_daily