Manually calculating power consumption of lights

Purely out of interest, I tried to automatically collect the measured values for one of my lamps.
At least this seems to work quite reliably:

It has now taken about 10 hours to collect ~1000 data points. So I just need to run it for ~2 months to have all the data. :smiley:

Of course, that is totally impractical. I need to look again at what intervals might make sense for a coarser sampling.

@tbloth
The one in Grafana is actually not that exciting. There are only two queries, one for the current consumption and one for the total consumption, which go against my Postgres database where my Homeassistant sensor data is stored.
These are then simply two line graphs plotted against two y-axes.

One of these queries then looks like this, for example:

SELECT
  $__timeGroupAlias("time",$__interval,0)
  , 'Aktueller Verbrauch Spülmaschine'
  , AVG(state::FLOAT) AS value
FROM ltss
WHERE
  $__timeFilter("time")
  AND entity_id = 'sensor.stromverbrauch_spuehlmaschine'
  AND state != 'None'
GROUP BY 1,2
ORDER BY 1,2

Thanks. How do you sum up constantly on the actual wattage?

Quite simple. I use Fritz!DECT 200 plugs for my household appliances that determine the total consumption itself. :wink:

For my lamps, I only have a very rough approximation so far, in which I determine how long they have been on per year so far and then multiply that by the possible maximum consumption according to the manufacturer:

  - platform: history_stats
    name: Deckenlampe Büro AN Jahr
    entity_id: light.deckenlampe_buro
    state: 'on'
    type: time
    start: '{{ now().replace(month=1).replace(day=1).replace(hour=0).replace(minute=0).replace(second=0) }}'
    end: '{{ now() }}'

  - platform: template
    sensors:
      gesamtverbrauch_light_deckenlampe_buro:
        value_template: '{{ states("sensor.deckenlampe_buro_an_jahr") | multiply(0.0095) | round(2)}}'
        unit_of_measurement: 'kWh'
        friendly_name: 'Gesamtverbrauch Deckenlampe Büro'

ah, interesting. So you determine total power consumption via HA. Well, that’s tricky if I’d do so for all my lights

Hey @nodecentral, it may be your indenting. Have a look at everything under sensors:. They all need to have a double space added.

If you replace all of the code in your post with the following it should work.

sensor:
  - platform: snmp
    host: 10.10.10.103
    community: public
    baseoid: 3.6.1.2.1.1.1.0
  - platform: fixer
    api_key: 5609c7need-to-hide-this7bc2fc1f3a16
    target: USD
  - platform: template
    sensors:
      living_room_big_lamp_power2:
        friendly_name: Living Room Main Light Power
        icon_template: mdi:lightning-bolt
        unit_of_measurement: "W"
        value_template: >
          {% if is_state('light.living_room_light','on') %}
            {% set mired = state_attr("light.living_room_light","color_temp") %}
            {% set brightness = ((state_attr("light.living_room_light","brightness") - 3) * (100 - 1)) / (254 - 3) + 1 %}
            {% set m153 = (0.000435254 * (brightness**2) + (0.002105929 * brightness) + 1.500175321) %}
            {% set m203 = (0.00057964 * (brightness**2) + 0.001134825 * brightness + 1.527090668) %}
            {% set m253 = (0.000785198 * (brightness**2) - 0.002785236 * brightness + 1.57216088) %}
            {% set m303 = (0.000733083 * (brightness**2) - 0.002574077 * brightness + 1.573079888) %}
            {% set m353 = (0.000668279 * (brightness**2) - 0.001853391 * brightness + 1.561981489) %}
            {% set m403 = (0.000570268 * (brightness**2) + 0.0000671178176666897 * brightness + 1.527920206) %}
            {% set m454 = (0.000487813 * (brightness**2) - 0.000161527 * brightness + 1.518884275) %}
            {% if mired >= 153 and mired < 203 %}
              {{ ((((mired - 153) * (m203 - m153)) / (203 - 153)) + m153)|round(2) }}
            {% elif mired >= 203 and mired < 253 %}
              {{ ((((mired - 203) * (m253 - m203)) / (253 - 203)) + m203)|round(2) }}
            {% elif mired >= 253 and mired < 303 %}
              {{ ((((mired - 253) * (m303 - m253)) / (303 - 253)) + m253)|round(2) }}
            {% elif mired >= 303 and mired < 353 %}
              {{ ((((mired - 303) * (m353 - m303)) / (353 - 303)) + m303)|round(2) }}
            {% elif mired >= 353 and mired < 403 %}
              {{ ((((mired - 353) * (m403 - m353)) / (403 - 353)) + m353)|round(2) }}
            {% elif mired >= 403 and mired < 454 %}
              {{ ((((mired - 403) * (m454 - 403)) / (454 - 403)) + m403)|round(2) }}
            {% elif mired == 454 %}

@tbloth, I have the template below for calculating total light power based on the template sensor above, my naming convention of sensor.*light_power or sensor.*lamp_power, and inspiration from this post.

- platform: template
  sensors:
    sum_power_consumption_lights:
      friendly_name: "Total Light Power Consumption"
      unit_of_measurement: 'W'
      icon_template: mdi:home-lightbulb-outline
      value_template: >
        {%- macro wildcard(entity_ids, select1, select2) %}
          {%- for entity_id in entity_ids %}
            {% set state_value = states(entity_id) %}
            {%- if select1 in entity_id or select2 in entity_id %}
              {{- state_value }}{{ '' if loop.last else ',' }}
            {%- endif %}
          {%- endfor %}
        {%- endmacro %}

        {% set entity_ids = states.sensor | selectattr('attributes.device_class', '==', 'power') | map(attribute='entity_id') %}
        {{ wildcard(entity_ids, 'light_power','lamp_power').split(',') | map('float') | sum | round(2) }}
2 Likes

That’s quite interesting. So - what is the following actually doing?

{% set entity_ids = states.sensor | selectattr('attributes.device_class', '==', 'power') | map(attribute='entity_id') %}
{{ wildcard(entity_ids, 'light_power','lamp_power').split(',') | map('float') | sum | round(2) }}

You build a list of IDs that have “power” as part of the name? Like from your post “living_room_big_lamp_power”

Manually added it to my sensors now

        device_class: power

Nice, this is a good start. What sensor do you use to take the measurements? And how long do you need to wait between two measurements?
We definitely need to go a little coarser, as 2 months will be impractable.

I’m defining a sensor as I mostly took from here, such as:
All my lights are connected to my ABB Free@Home, so I had to define a sensor just for the wattage manually for every light. But I can ‘measure’ when they are on or off.

## Entrance
      watt_l_entry:
        unit_of_measurement: "W"
        device_class: power
        value_template: >-
          {% if is_state('light.l_entry', 'on') %}
            5.5
          {% else %}
            0
          {% endif %}

From here I went to what @cloak posted

sum_power_consumption_lights:
        friendly_name: "Total Light Power Consumption"
        unit_of_measurement: 'W'
        icon_template: mdi:home-lightbulb-outline
        value_template: >
          {%- macro wildcard(entity_ids, select1) %}
            {%- for entity_id in entity_ids %}
              {% set state_value = states(entity_id) %}
              {%- if select1 in entity_id in entity_id %}
                {{- state_value }}{{ '' if loop.last else ',' }}
              {%- endif %}
            {%- endfor %}
          {%- endmacro %}

          {% set entity_ids = states.sensor | selectattr('attributes.device_class', '==', 'power') | map(attribute='entity_id') %}
          {{ wildcard(entity_ids, 'watt_l').split(',') | map('float') | sum | round(2) }}

history_stats is next oin my agenda. Just dunno how :slight_smile:

I simply plugged a table lamp for E14 sockets into my Fritz!DECT 200 socket.
The Fritz socket does not have the most accurate electricity meter and the snychronization with Home Assistant takes a while, but for the test it fits for now. My task currently always sleeps 30 seconds between switching the lamp and recording the consumption values.
Currently I’m running it again with an interval of 5 for brightness and 6 for Mired values. This seems to be quite a good compromise.

1 Like

Hi @cloak

Thanks so much…

I pasted your version straight in to the config.yaml but I still get an error - (see below) - any other ideas?

Logger: homeassistant.components.homeassistant
Source: components/homeassistant/__init__.py:138 
Integration: Home Assistant Core Integration (documentation, issues) 
First occurred: 18 May 2021, 23:04:28 (2 occurrences) 
Last logged: 21:15:06

Invalid config for [sensor.template]: expected dictionary for dictionary value @ data['sensors']. Got None extra keys not allowed @ data['living_room_big_lamp_power']. Got OrderedDict([('friendly_name', 'Living Room Main Light Power'), ('icon_template', 'mdi:lightning-bolt'), ('unit_of_measurement', 'W'), ('value_template', '{% if is_state(\'light.living_room_light\',\'on\') %}\n {% set mired = state_attr("light.living_room_light","color_temp") %}\n {% set brightness = ((state_attr("light.living_room_light","brightness") - 3) * (100 - 1)) / (254 - 3) + 1 %}\n {% set m153 = (0.000435254 * (brightness**2) + (0.002105929 * brightness) + 1.500175321) %}\n {% se.... (See ?, line ?).
Invalid config for [sensor.template]: invalid template (TemplateSyntaxError: Unexpected end of template. Jinja was looking for the following tags: 'elif' or 'else' or 'endif'. The innermost block that needs to be closed is 'if'.) for dictionary value @ data['sensors']['living_room_big_lamp_power2']['value_template']. Got '{% if is_state(\'light.living_room_light\',\'on\') %}\n {% set mired = state_attr("light.living_room_light","color_temp") %}\n {% set brightness = ((state_attr("light.living_room_light","brightness") - 3) * (100 - 1)) / (254 - 3) + 1 %}\n {% set m153 = (0.000435254 * (brightness**2) + (0.002105929 * brightness) + 1.500175321) %}\n {% set m203 = (0.00057964 * (brightness**2) + 0.001134825 * brightness + 1.527090668) %}\n {% set m253 = (0.000785198 * (brightness**2) - 0.002785236 * bright.... (See ?, line ?).

It’s doing a wildcard text search looping through the entity_ids for entities with light_power and lamp_power in their names, then adding their state (power) to a list for summing. Given i’ve already filtered for the domain power, i really only need to have to do a search for light and lamp.
I searched for ages to find a wildcard string search using Jinja2 filters, but couldn’t find anything. More than happy if someone can simplfy the code!

yeah, exactly. And for other integrations, like my Smartthigns Smart Plugs, i’ve gone with a similar naming convention to lamp_power and light_power and added the following to customize_glob.yaml. It’s not clear to me why I can do a wildcard string in customize glob, but not in a filter.

"sensor.*_outlet_power":
  device_class: power
1 Like

hopefully this is it - copy paste error on my part. Add the following to the bottom of the code i replied with earlier, but with appropriate indenting - refer this post

            {{ m454|round(2) }}
          {% endif %}
        {% else %}
        {{ 0.00 }}
        {% endif %}

Thanks @cloak

That did it, no error and I can see it listed under sensors on the UI, however when I turn the light on it goes from 0.0 W to ‘Unavailable’ ? So something is still not quite right.

The hue living room light entity is as follows…

Extract of the code.

value_template: >
          {% if is_state('light.living_room_light','on') %}
            {% set mired = state_attr("light.living_room_light","color_temp") %}
            {% set brightness = ((state_attr("light.living_room_light","brightness") - 3) * (100 - 1)) / (254 - 3) + 1 %}

Should “color_temp” be “color_mode” and/or the 254 value changed to 255 ?

Hey @nodecentral, what bulb do you have? The template is for a Hue LTW001.
Does your bulb have the capability to change warmth (mired) or is it just brightness?
If just brightness, the template won’t work

I don’t think color_temp should be color_mode. Color_mode sounds like an actual mode. And 254 should probably change to 255, but it may be a mute point

Hi @cloak

Checking all my hue bulbs I sadly done have that specific model.

Is it possible to adapt this code to focus on the brightness of the bulb to calculate the watts ? Even if we started off with a very rudimentary calculator , that takes a # watt hue bulb (e.g 9w) and do some calculations based on the brightness level ? 255 = 9 watts, 128 = 4.5w etc etc

So here is my first automatically generated data set. :slight_smile:
bulbs_consumption_values/E27_amber_tunable_white_Globe_G95.csv at main · CM000n/bulbs_consumption_values (github.com)

The collection for this has now taken about 30 hours.
I think I will also run another test for one of my Hue bulbs tonight or tomorrow. This is certainly more interesting to most people than my SHYNE Globe that I tested the whole thing with. :wink:

1 Like

Hey @nodecentral, do any of the bulbs give a warmth value in the specs? If so, then the code should be able to be adapted.

How would you generate historical stats on a total consumption?
I like what Simon proposed. Unfortunately, that only applies to a single light

- platform: history_stats
    name: Deckenlampe Büro AN Jahr
    entity_id: light.deckenlampe_buro
    state: 'on'
    type: time
    start: '{{ now().replace(month=1).replace(day=1).replace(hour=0).replace(minute=0).replace(second=0) }}'
    end: '{{ now() }}'

  - platform: template
    sensors:
      gesamtverbrauch_light_deckenlampe_buro:
        value_template: '{{ states("sensor.deckenlampe_buro_an_jahr") | multiply(0.0095) | round(2)}}'
        unit_of_measurement: 'kWh'
        friendly_name: 'Gesamtverbrauch Deckenlampe Büro'

From what has been given by @cloak, I can do such history_stats, no? Since it has no ‘state’

sum_power_consumption_lights:
        friendly_name: "Total Light Power Consumption"
        unit_of_measurement: 'W'
        icon_template: mdi:home-lightbulb-outline
        value_template: >
          {%- macro wildcard(entity_ids, select1) %}
            {%- for entity_id in entity_ids %}
              {% set state_value = states(entity_id) %}
              {%- if select1 in entity_id in entity_id %}
                {{- state_value }}{{ '' if loop.last else ',' }}
              {%- endif %}
            {%- endfor %}
          {%- endmacro %}

          {% set entity_ids = states.sensor | selectattr('attributes.device_class', '==', 'power') | map(attribute='entity_id') %}
          {{ wildcard(entity_ids, 'watt_l').split(',') | map('float') | sum | round(2) }}

Any ideas?