Last active
November 3, 2025 16:28
-
-
Save danielolsson100/022e8cc03db9c92e9289f42d1c6ee32b to your computer and use it in GitHub Desktop.
Logic to minimize the tariff costs in Home Assistant
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment