Statistics in a Sensor - template

Configuration
1

Hi,

I would like to generate a dynamic monthly report (ie since month start) based on various statistics on different measures like temperature, electricity consumption…

I cannot seem to get a decent way to create statistics on a defined period like the statistics-graph or statistic cards would do.

I have tried:

  • statistics helper, but it runs on fixed duration;
  • history_stats, but it’s only about duration or count of states, what I need is average, min, mix
  • SQL integration (advised by AI), but it cannot combine short-term and long-term data

Also, my recorder is kept on 14 days to keep my db size under control.

Has anyone achieved this?
My goal is to include these statistics in template sensors I could publish any way I want.

Thanks (and Happy New Year)

2

tbh I am not sure what you want to achieve, you seem to be happy with statistics-graph or similar… so what is this ‘report’ that you would like to see ? Small example of layout? The better you detail, the easier it is to answer :slight_smile:

3

Hi

Basically, I intend to include the values in a markdown card as well as an email notification.

The idea is to create some storytelling like “The average temperature this month is x.x °c, which is y.y °c more than last month”

4

This third party integration can average over a defined period (e.g. current month or last month).

it hasn’t been updated in quite a while but there don’t appear to be any show stopping issues open.

You would have to adjust your recorder to keep state data for at least the length of time you want to average over as this integration uses state data that is purged after 10 days by default.

5

Assuming your sensor is set up appropriately to record statistics, you can use the recorder.get_statistics action to query the info you want. And the statistics are retained indefinitely, so it doesn’t matter what your history purge settings are.

To try it out, go to developer tools → actions, choose recorder.get_statistics, and fill out the appropriate info. In your case you’d choose your temperature sensor entity, period = month, and type = mean.

After you execute it, you can select “copy to clipboard as template” and then switch over to the template tab, and paste it there. Then you can play around with the template to get what you need.

A template like this (pasted into the template window after the action response) could get you started:

{% set temps = action_response.statistics['sensor.temperature'] | map(attribute='mean') | list %}
{% set diff = (temps[1]-temps[0])|round(1) %}
The average temperature this month is {{ temps[1]|round(1) }}°c, which is {{ diff|abs }}°c {{ 'warmer' if diff >= 0 else 'colder' }} than last month”
2 Likes
6

Thank you!
That seems to be the way to go.
With such an action I am getting the sliding stats per month I was looking for.

      - action: recorder.get_statistics
        data:
          start_time: >
            {{ (now().replace(day=1, hour=0, minute=0, second=0) - timedelta(days=1)).replace(day=1) }}
          end_time: "{{ now() }}"
          statistic_ids:
            - sensor.thermometre_inside
            - sensor.thermometre_outside
          period: month
          types: [mean, min, max]
        response_variable: stats

I just need to process them in a template. Looks perfect

2 Likes
7

this seems to be near to what I am looking for… please check if that is the case :wink:

using the SolarEdge integration we gt statistics, and can show these in a statistics-graph with this:

      type: statistics-graph
      chart_type: line
      period: hour
      stat_types:
        - change
      days_to_show: 7
    title: Uur productie ZW
    entities: &zw_entities
      - entity: solaredge:<site-id>_<panel_id>
        name: 1.0.9
      - entity: solaredge:<site-id>_<panel_id>
        name: 1.0.3
      - entity: solaredge:<site-id>_<panel_id>
        name: 1.0.2
      - entity: solaredge:<site-id>_<panel_id>
        name: 1.0.4
      - entity: solaredge:<site-id>_<panel_id>
        name: 1.0.9

and we can find these statistics also in the Statistics tool in developer

Above shows like this (snow covered…):

Scherm­afbeelding 2026-01-07 om 15.00.44
Scherm­afbeelding 2026-01-07 om 15.00.441070×728 144 KB

However, we can not use them in any card that requires actual entities, and that is what Id love to do.

Be able to check the current state of each of my panels in a nice UI, and not perse the graph. Update each 15 minutes or so, or, if that cant be done, use the system options for 5 minutes or 1 hour.

for that I suppose I need an SQL query and create the entities out of them some way or another. Maybe like above, with the response_variable and a template. Or, maybe a SQL sensor entity?

How can I go about creating that, I am not experienced at all using these queries, and find them hard to test in the UI.

Please have a look? thanks!

8

I combine the get_stastics with a template creation (via YAML)

template:
  - trigger:
      # On start of Home Assistant
      - platform: homeassistant
        event: start
      # Every 30 minutes
      - platform: time_pattern
        minutes: "/30"

    action:
      - action: recorder.get_statistics
        response_variable: stats
        data:
          start_time: >
            {{ (now().replace(day=1, hour=0, minute=0, second=0)
                - timedelta(days=1)).replace(day=1) }}
          end_time: "{{ now() }}"
          types: [state]
          period: month
          statistic_ids:
            - sensor.sma_solar_total_yield

    sensor:

      - name: "Solar Production - Current Month"
        unique_id: solar_production_current month
        unit_of_measurement: "kWh"
        device_class: energy
        state_class: measurement
        icon: mdi:solar-power
        state: >
          {{
            ((stats.statistics['sensor.sma_solar_total_yield'][-1].state | float)
             - (stats.statistics['sensor.sma_solar_total_yield'][-2].state | float)) | round(2)
          }}
        attributes:
          description: "Solar production current month based on last index vs previous index"

You can fine tune your get_stastics action in the developer tools UI, and copy/paste the result in your template.

There is a way to reach a similar goal through UI, but it requires you to create an input_number helper per measure you want to keep and display.

I am not sure of which value/period you want to update every 15 minutes

1 Like
9

thanks, that is the exact thing I ws just experimenting with, but could find the right start time template yet…

template:
  - triggers:
      - trigger: time_pattern
        minutes: 0
      - trigger: homeassistant
        event: start
      - trigger: event
        event_type: event_template_reloaded

    actions:
      - action: recorder.get_statistics
        data:
          start_time: '{{ now()- timedelta(hours=1)  }}'
          end_time: '{{ now() }}'
          statistic_ids:
            - solaredge:<site-id>_<panel_id>
            - solaredge:<site-id>_<panel_id>2
            - solaredge:<site-id>_<panel_id>3
            - solaredge:<site-id>_<panel_id>4
            - solaredge:<site-id>_<panel_id>5
          period: hour
          types:
            - state
          response_variable: optimizer_stats

    sensor:
      - name: SolarEdge optimizer 1.0.8
        unique_id: solaredge_optimizer_1.0.8_statistics
        unit_of_measurement: 'W'
        device_class: power
        state_class: measurement
        state: >
          {{ optimizer_stats['solaredge:<site-id>_<panel_id>'][0].mean | round(0) }}
        attributes:
#           min_power: '{{ stats['<site-id>_<panel_id>'][0].min | round(0) }}'
#           max_power: '{{ stats['<site-id>_<panel_id>'][0].max | round(0) }}'
          last_updated: '{{ now() }}'

but have to wait another hour to check it :wink:

Idealy id update each 5 minutes, but I dont think the statistics have that. I can only get a result when I use the hour as minimal period?

there were several syntax issues in the above, this does actually produce:

template:
  - triggers:
      - trigger: time_pattern
        minutes: 0
      - trigger: homeassistant
        event: start
      - trigger: event
        event_type: event_template_reloaded

    actions:
      - action: recorder.get_statistics
        response_variable: optimizer_stats
        data:
          start_time: '{{ now()- timedelta(hours=1)  }}'
          end_time: '{{ now() }}'
          statistic_ids:
            - solaredge:<site-id>_<panel_id>
            - solaredge:<site-id>_<panel_id>2
            - etc
          period: hour
          types:
            - state
            - sum

    sensor:
      - name: SolarEdge optimizer 1.0.8
        unique_id: solaredge_optimizer_1.0.8_statistics
        unit_of_measurement: 'W'
        device_class: power
        state_class: measurement
        state: >
          {{ optimizer_stats.statistics['solaredge:<site-id>_<panel_id>'][0].state | round(0) }}
        attributes:
          sum: >
            {{ optimizer_stats.statistics['solaredge:<site-id>_<panel_id>'][0].sum | round(0) }}
          last_updated: '{{ now() }}'

not sure what the sum is really. is it an actual useful number, or should I stay with the state only.

10

A query like this:

action: recorder.get_statistics
data:
  statistic_ids:
    - sensor.p1_meter_importation_d_energie
  period: 5minute
  types:
    - state
  start_time: "{{ (now() - timedelta(minutes=20)).strftime('%Y-%m-%d %H:%M:%S') }}"

every 15 minutes would give a result like this

statistics:
  sensor.p1_meter_importation_d_energie:
    - start: "2026-01-07T17:00:00+00:00"
      end: "2026-01-07T17:05:00+00:00"
      state: 38928.304
    - start: "2026-01-07T17:05:00+00:00"
      end: "2026-01-07T17:10:00+00:00"
      state: 38928.373
    - start: "2026-01-07T17:10:00+00:00"
      end: "2026-01-07T17:15:00+00:00"
      state: 38928.464
11

dont you need to configure the endtime there?

12

I believe it takes now() by default

13

hmm, cant make that work.
as a mater of fact, the statistics seem to have died upon me completely now, nothing is available in the statistics tool for the panels currently. Might be the login for them I need to check once more, SolarEdge is acting up…

for config, I now use

    actions:
      - action: recorder.get_statistics
        response_variable: optimizer_stats
        data:
          start_time: '{{ now()- timedelta(hours=1)  }}'
          end_time: '{{ now() }}'
          period: hour
          types:
            - state
            - sum
          statistic_ids:

and the yaml for my panels has been simplified using a var and anchors:

    sensor:

##########################################################################################
# ZW
##########################################################################################
      - name: SolarEdge optimizer 1.0.8
        unique_id: solaredge_optimizer_1.0.8_statistics
        variables:
          stats: >
            {{optimizer_stats.statistics.get('solaredge:<site-id>_25176727',[])}}
        <<: &optimizer_config
          unit_of_measurement: W
          device_class: power
          state_class: measurement
          state: >
            {{stats[0].state|round(0) if stats|length > 0 else -1}}
          attributes:
            sum: >
              {{stats[0].sum|round(0) if stats|length > 0 else 'Niet beschikbaar'}}
            last_updated: '{{ now() }}'

and repeat that simply per panel, only requiring 1 change for the panel_id:

      - name: SolarEdge optimizer 1.0.3
        unique_id: solaredge_optimizer_1.0.3_statistics
        variables:
          stats: >
            {{ optimizer_stats.statistics.get('solaredge:<site-id>_257892242', []) }}
        <<: *optimizer_config

      - name: SolarEdge optimizer 1.0.2
        unique_id: solaredge_optimizer_1.0.2_statistics
        variables:
          stats: >
            {{ optimizer_stats.statistics.get('solaredge:<site-id>_25025435', []) }}
        <<: *optimizer_config
etcetc