With the help of Home-Assistant templates, data from various sensors can be converted, combined or provided with your own formulas. Templates allow the creation of logic blocks for use in custom sensors, conditions or for customizing specific values in Lovelance Cards. The syntax used is Jinja2, a fully functional template engine for Python.

Where can templates be used?

Templates can be used in the following Home Assistant components:

• Developer tools / TEMPLATE
• Template sensors (under Helpers)
• automations
• certain Lovelance Cards

Developer tools

If you want to create a new sensor based on a template, you can do this in the settings under "Devices & services", "CREATE HELPER" and "Template":

Template sensor: Helper

Originally, template-sensors were stored in the configuration.yaml file. In current Home Assistant versions, the templates can be conveniently created as helpers in the Home Assistant interface. The helpers already show the calculated values or any syntax errors when they are created.

Menu item: Settings, Create helper -> Template:

You can select the options for a binary sensor, i.e. "On" and "Off", or any other sensor:

Within the automations, templates can be used as triggers, conditions and in the conditions of actions:

See also: Home Assistant automation - possibilities & basics

Template examples

As a concrete example of a template sensor, the time could be made available as a separate entity from the date and time for the next sunrise. The integrated sensor for the sunrise is available in the following format: 2023-09-12T07:40:21.796007+00:00. The following template can be used to extract the time:

{{
as_timestamp(states.sun.sun.attributes.next_rising) | 
timestamp_custom('%H:%M')
}}

"as_timestamp" thereby converts the value ("states.") of the sensor "sun.sun.attributes.next_rising" into a Unix timestamp. A pipe "|" to the function "timestamp_custom" converts the timestamp back into "hours": "minutes": '%H:%M'. As already mentioned, the template can be tested in the developer tools:

Used as a template sensor, this also shows a preview of the result:

When the template sensor is saved, a new sensor with the ID: sensor.name, in this case "sensor.sunrise", is created and is available as a separate entity in Home Assistant. For more information on template sensors and their unit of measurement, device and status class, see the following page: Home Assistant Sensors - Basics

Template - Cheat Sheet

Here are a few sample templates:

{{ as_timestamp(states.sun.sun.attributes.next_rising) | timestamp_custom(' %H:%M') | replace(" 0", "") }}

 {{ as_timestamp(states.sun.sun.attributes.next_setting) | timestamp_custom(' %H:%M') | replace(" 0", "") }}

 {{ as_timestamp(states.sun.sun.attributes.next_rising) | timestamp_custom(' %H:%M') | replace(" 0", "") }} -
 {{ as_timestamp(states.sun.sun.attributes.next_setting) | timestamp_custom(' %H:%M') | replace(" 0", "") }}

{{ as_timestamp(states.sun.sun.attributes.next_rising) | timestamp_custom(' %H:%M') | replace(" 0", "") }} - 
{{ as_timestamp(states.sun.sun.attributes.next_setting) | timestamp_custom(' %H:%M') | replace(" 0", "") }}

{{ as_timestamp(now()) | timestamp_custom('%H%M') | float  > (states.sun.sun.attributes.next_rising |as_timestamp) | timestamp_custom("%H%M", True) | float }}

{{ as_timestamp(now() - timedelta(minutes=180))  | timestamp_custom('%H%M') | float  > (states.sun.sun.attributes.next_rising |as_timestamp) | timestamp_custom("%H%M", True) | float}}

{{ as_timestamp(now()) | timestamp_custom('%H%M') | float  > (states.sun.sun.attributes.next_setting |as_timestamp) | timestamp_custom("%H%M", True) | float }}

{% set now = (as_timestamp(now()) | timestamp_custom('%H:%M')) %}
{% set sr = (as_timestamp(states.sun.sun.attributes.next_rising) | timestamp_custom('%H:%M')) %}  
{% set ss = (as_timestamp(states.sun.sun.attributes.next_setting) | timestamp_custom('%H:%M')) %} 
{% set start = now if now > sr else sr %}
{% if now < ss %}
{{ (((ss.split(":")[0] | int * 60 + ss.split(":")[1] | int) - (start.split(":")[0] | int * 60 + start.split(":")[1] | int)) / 60) | round(2) }}
{% else %}
0
{% endif %}

{% set V = states('sensor.meter01_voltage_l1') | float %}
{% set A = states('sensor.meter01_current_l1') | float %}
{{ ( 0.9 * A * V) | round(0)}}

{% set value = states('sensor.heating_active_power') | float %}
          {{ "off" if value < 10 else 
          "pump" if value < 58 else 
          "water" if value < 70 else 
          "pump" if value < 125 else 
          "heat" }}

{{ (((as_timestamp(now()) - 
as_timestamp(states.switch.relay_heizung_puffer_kalt.last_changed)) / 60) | int) 
  > 30 }} 

{% set pumpen = states('sensor.heizung_pumpe_eg_active_power') | float if states('sensor.heizung_pumpe_eg_active_power') | is_number else 0   %}

Use previous value in template sensor

Using the following template, a template sensor (here sensor.lastavailablestate) can return the last value that was not "unavailable":

{% if states('sensor.sometimes_unavailable')  != 'unavailable'%}
    {{ sensor.sometimes_unavailable }}
{% else %}
    {{ states('sensor.lastavailablestate') }}
{% endif %}

In addition to the template sensors, there are additional helper sensors for specific data in Home Assistant:

specific helpers

Add Riemann sum integral sensor (formerly Platform:Integration)

In order to determine the consumption from an instantaneous value, a so-called Riemann sum integral sensor can be added. This allows the complete consumption to be calculated from certain consumption values: Watt, the complete consumption can be totaled as Wh (watt-hours):

Consumption is summed:

Change Riemann sum integral sensor method: trapezoidal vs. left

If the "trapezoidal rule" is used as the method for the Riemann sum integral sensor, a spike can cause the energy meter to count too much:

Unfortunately, there is no option in the graphical user interface to change the method of the sum integral sensor. A change is only possible directly in the configuration via an editor:

geändert in "left"

The "Platform Integration" can be used as a "utility_meter" to relate the totalized consumption to a specific time period:

Add Utility_Meter

The utility meter uses the total consumption and divides it into specific time periods: e.g. hourly or daily:

Statistics Platform

The Statistics Platform can be used to represent certain historical values, for example the sum of certain values. Currently, these must be added in the configuration.yaml file, as there are no helper functions for this in the GUI yet, only a feature request: https://community.home-assistant.io/t/make-a-statistics-helper/610340:

sensor:
  - name: rain_weather_24
    platform: statistics
    entity_id: sensor.rain_weather
    state_characteristic: sum
    max_age:
      hours: 24

sum_differences uses the differences between the values in a continuous counter:

sensor:
  - name: powerplug_24
    platform: statistics
    entity_id: sensor.powerplug_summation_delivered
    state_characteristic: sum_differences	
    max_age:
      hours: 24

Another example is the average temperature of the last 24 hours

sensor:
  - name: out_temperature_24
    platform: statistics
    entity_id: sensor.out_temperature
    state_characteristic: mean
    max_age:
      hours: 24

SQL integration

In addition, the data for certain sensors can be read directly from the database via SQL integration. This means, for example, that statistical data can also be queried retrospectively without having to create a corresponding sensor beforehand.

SELECT (Max(state) - Min(state)) as val FROM statistics 
WHERE metadata_id = (SELECT id FROM statistics_meta 
WHERE statistic_id = 'sensor.household_energy') and 
created_ts > (SELECT unixepoch(date('now'),'-1 days')) and 
created_ts < (SELECT unixepoch(datetime(date('now'))))

SELECT (Max(state) - Min(state)) as val 
FROM statistics_short_term 
WHERE metadata_id = (SELECT id FROM statistics_meta 
WHERE statistic_id = 'sensor.household_energy') and 
created_ts > (SELECT unixepoch(date('now'))) and 
created_ts < (SELECT unixepoch(datetime('now')))

SELECT (Max(state) - Min(state)) as val FROM statistics_short_term 
WHERE metadata_id = (SELECT id FROM statistics_meta 
WHERE statistic_id = 'sensor.household_energy') and 
created_ts > (SELECT unixepoch(date('now'),'-1 days')) and 
created_ts < (SELECT unixepoch(datetime('now'),'-1 days'));

SELECT (Max(state) - Min(state)) as val FROM statistics_short_term 
WHERE metadata_id = (SELECT id FROM statistics_meta 
WHERE statistic_id = 'sensor.pv_panels_energy') and 
created_ts > (SELECT unixepoch(datetime('now'),'-1 days')) and 
created_ts < (SELECT unixepoch(date('now'))) ;

SELECT (Max(state) - Min(state)) as val FROM states  
WHERE metadata_id = (SELECT metadata_id FROM states_meta  
WHERE entity_id = 'sensor.pv_panels_energy') and 
last_updated_ts  > (SELECT unixepoch(datetime('now'),'-1 days')) and 
last_updated_ts  < (SELECT unixepoch(date('now')));

SELECT min("state") as val FROM states 
WHERE metadata_id = (SELECT metadata_id FROM states_meta 
WHERE entity_id = 'sensor.byd_available_capacity') and 
last_updated_ts > (SELECT unixepoch(date('now'),'-1 days')) and 
last_updated_ts < (SELECT unixepoch(date('now')))

SELECT CAST(state AS INTEGER) as val FROM states 
WHERE metadata_id = (SELECT metadata_id FROM states_meta 
WHERE entity_id = 'sensor.byd_battery_box_premium_hv_ladezustand') and 
last_updated_ts > (SELECT unixepoch(date('now'),'-1 days')) and 
last_updated_ts < (SELECT unixepoch(date('now')))
order by val limit 1

SELECT state as val FROM states 
WHERE metadata_id = (SELECT metadata_id FROM states_meta 
WHERE entity_id = 'sensor.byd_battery_box_premium_hv_ladezustand') and 
last_updated_ts > (SELECT unixepoch(datetime('now'),'-1 days'))
order by last_updated_ts limit 1

SELECT last_updated_ts as val FROM states 
WHERE metadata_id = (SELECT metadata_id FROM states_meta 
WHERE entity_id = 'sensor.fronius_storage_chast') and
state = "FULL"  
order by last_updated_ts DESC limit 1

SELECT datetime(last_updated_ts, 'unixepoch', 'localtime') as val FROM states 
WHERE metadata_id = (SELECT metadata_id FROM states_meta 
WHERE entity_id = 'sensor.fronius_storage_chast') and
state = "FULL"  
order by last_updated_ts DESC limit 1

SELECT (Max(state) - Min(state)) as val 
FROM statistics_short_term 
WHERE metadata_id = (SELECT id FROM statistics_meta 
WHERE statistic_id = 'sensor.household_energy') and 
created_ts > (
    (SELECT last_updated_ts as val FROM states 
    WHERE metadata_id = (SELECT metadata_id FROM states_meta 
    WHERE entity_id = 'sensor.fronius_storage_chast') and
    state = "FULL"  
    order by last_updated_ts DESC limit 1
)) and 
created_ts < (SELECT unixepoch(datetime('now')))

SELECT max(CAST(state AS FLOAT)) as val FROM states 
WHERE metadata_id = (SELECT metadata_id FROM states_meta 
WHERE entity_id = 'sensor.pv_panels_energy_forecast_today_remaining') and state != "unknown" and 
state != "unavailable" and strftime('%H', datetime(last_updated_ts, 'unixepoch')) = strftime('%H', datetime("now"));

SELECT max(CAST(state AS FLOAT)) as val,  datetime(last_updated_ts, 'unixepoch','localtime') FROM states 
WHERE metadata_id = (SELECT metadata_id FROM states_meta 
WHERE entity_id = 'sensor.pv_panels_energy_today') and state != "unknown" and 
state != "unavailable" and 
CAST(strftime('%H%M',datetime(last_updated_ts, 'unixepoch','+2 hours')) AS FLOAT) < CAST( strftime('%H%M', datetime('now','+2 hours')) AS FLOAT);

SELECT min(CAST(state AS FLOAT)) as val,  datetime(last_updated_ts, 'unixepoch','localtime') FROM states 
WHERE metadata_id = (SELECT metadata_id FROM states_meta 
WHERE entity_id = 'sensor.pv_panels_energy_yesterday_remaining') and state != "unknown" and 
state != "unavailable" and 
CAST(strftime('%H%M',datetime(last_updated_ts, 'unixepoch','+2 hours')) AS FLOAT) < CAST( strftime('%H%M', datetime('now','+2 hours')) AS FLOAT);

Unexpected behavior date() vs datetime()

"date()" in the queries uses the currently set time zone, whereas datetime() uses UTC. Comparisons or outputs with "date()" in the queries therefore have an offset compared to the timestamps in the database: When using GMT+1DST as the time zone, unixepoch(date('now','+2 hours')) can be used for the current day to correct the offset. unixepoch(date('now','-22 hours')) would be yesterday accordingly. After the changeover to daylight saving time, the offset then changes to unixepoch(date('now','+1 hours')) ...

A workaround with date("now", "utc") does not work either, as "utc" is ignored at this point when using the Home Assistant SQL integration. (It would work in an SQLite browser). I have also created an issue on Github and will update this article accordingly if anything new comes up: https://github.com/home-assistant/core/issues/124702.