Mastering Long-Term Statistics and Custom Reporting in Home Assistant

Unlocking Deeper Insights: Mastering Long-Term Statistics and Custom Reporting in Home Assistant

While Home Assistant excels at real-time control and immediate automations, its true power for optimization and understanding your home often lies in its ability to collect, store, and analyze historical data. Beyond simple history graphs, Home Assistant provides robust tools for long-term statistics and even custom reporting, allowing you to identify trends, optimize energy usage, track resource consumption, and make data-driven decisions about your smart home.

The Core Pillars: Recorder, Utility Meter, and Statistics

At the heart of Home Assistant's data capabilities are a few key components:

• Recorder: This integration is responsible for saving your entity states and events to the Home Assistant database (default SQLite, or external MariaDB/PostgreSQL). It's the foundation for all historical data.
• Utility Meter: Designed specifically for tracking cumulative consumption over defined periods (daily, weekly, monthly, yearly, etc.), like energy, water, or gas.
• Statistics: Home Assistant automatically aggregates data from supported sensors into long-term statistics, which are highly optimized for historical charting and analysis.

Tracking Consumption with the Utility Meter Integration

The utility_meter integration is indispensable for monitoring cumulative values like electricity consumption (kWh), water usage (liters/gallons), or gas (m³). It takes an input sensor (e.g., an energy meter's total consumption) and resets its counter at specified intervals, providing you with consumption for that period.

Setup Steps for Utility Meter:

Add the following to your configuration.yaml or a dedicated package file:

utility_meter:
  daily_energy_consumption:
    source: sensor.total_house_energy_meter
    cycle: daily
  monthly_water_usage:
    source: sensor.main_water_meter_total
    cycle: monthly
  yearly_gas_usage:
    source: sensor.gas_meter_total
    cycle: yearly
    tariffs:
      - summer
      - winter

Explanation:

• daily_energy_consumption: A unique name for your utility meter.
• source: The entity_id of your cumulative sensor (e.g., from an energy monitor, smart plug, or water meter). This sensor must have the state_class: total_increasing or state_class: total attribute for correct long-term statistics generation.
• cycle: Defines the reset interval (daily, weekly, monthly, yearly, hourly, quarter-hourly).
• tariffs (optional): Allows tracking consumption during different periods (e.g., peak/off-peak energy rates). You'd typically use automations to switch these tariffs.

After configuration, restart Home Assistant. New entities like sensor.daily_energy_consumption, sensor.monthly_water_usage, etc., will appear, providing consumption values for their respective cycles.

Leveraging Home Assistant's Built-in Statistics

Home Assistant automatically generates long-term statistics for sensors that provide a `state_class` attribute (e.g., `measurement`, `total_increasing`, `total`). These statistics are highly optimized for historical charting and are used by dashboards like the Energy Dashboard.

Visualizing Statistics with the Statistics Graph Card:

The easiest way to visualize these statistics is using the 'Statistics Graph Card' in Lovelace. Add a new card to your dashboard and select this type. Then, choose the entities for which you want to display statistics (e.g., your utility meter sensors, or any other sensor with long-term statistics).

type: statistics-graph
entities:
  - sensor.daily_energy_consumption
  - sensor.monthly_water_usage
stat_types:
  - sum
period: week

This card offers various aggregation types (mean, min, max, sum) and time periods (hour, day, week, month, year), making it powerful for trend analysis.

Advanced Reporting with the SQL Sensor

For truly custom insights, such as calculating averages over specific periods, identifying peak usage times, or combining data from multiple entities in complex ways, the SQL integration is your best friend. This requires that your Home Assistant's recorder is configured to use an external database like MariaDB or PostgreSQL, as SQLite (the default) is not easily queryable externally by Home Assistant itself via the SQL sensor.

Prerequisites: Ensure your recorder is configured for an external database. If not, this is a separate advanced topic, but assuming you have it, you can proceed.

Setup Steps for SQL Sensor:

Add the following to your configuration.yaml or a dedicated package file:

sensor:
  - platform: sql
    db_url: !secret recorder_db_url # Or your full connection string
    queries:
      - name: 'Average Daily Living Room Temperature Last Week'
        query: >
          SELECT AVG(state) FROM states
          WHERE entity_id = 'sensor.living_room_temperature'
          AND created > DATE('now', '-7 days')
          AND state_id IS NOT NULL
        column: 'AVG(state)'
        unit_of_measurement: '°C'
        value_template: "{{ value | round(1) }}"
      - name: 'Peak Daily Energy Consumption This Month'
        query: >
          SELECT MAX(state) FROM states
          WHERE entity_id = 'sensor.daily_energy_consumption'
          AND created >= strftime('%Y-%m-01 00:00:00', 'now', 'localtime')
          AND state_id IS NOT NULL
        column: 'MAX(state)'
        unit_of_measurement: 'kWh'
        value_template: "{{ value | round(2) }}"

Explanation:

• platform: sql: Specifies the integration.
• db_url: Your database connection string. It's best practice to store this in secrets.yaml.
• queries: A list of queries to execute.
• name: The name of the resulting sensor entity (e.g., sensor.average_daily_living_room_temperature_last_week).
• query: The SQL query itself.
• column: The name of the column from the query result to use as the sensor's state.
• unit_of_measurement (optional): Defines the unit.
• value_template (optional): A Jinja2 template to process the raw result, useful for rounding or formatting.

Important SQL Notes:

• The states table stores sensor values. entity_id is the entity's unique ID.
• created is the timestamp of the state.
• Use state for the sensor's value.
• state_id IS NOT NULL filters out unavailable/unknown states.
• Date functions vary slightly between database types (e.g., DATE('now', '-7 days') for SQLite, NOW() - INTERVAL '7 days' for PostgreSQL, CURDATE() - INTERVAL 7 DAY for MariaDB). Adapt your queries to your specific database.

After configuring, restart Home Assistant. You'll get new sensors reflecting the results of your SQL queries, updated periodically (default is every 30 seconds). These can then be used in automations, Lovelace cards, or further calculations.

Best Practices for Reliable Data and Reporting

To ensure your long-term statistics and custom reports are accurate and useful:

• Ensure Accurate Source Sensors: The quality of your output depends entirely on the quality of your input. Use reliable sensors, ensure they report regularly, and calibrate them if necessary. For cumulative sensors, ensure they have the correct state_class: total_increasing.
• Handle Unavailable States: If source sensors go offline, your utility meters and statistics might produce unexpected spikes or flat lines. Consider using template sensors with `availability_template` or `filter` integrations to smooth out bad data points before feeding them to utility meters.
• Understand Data Retention: Home Assistant's recorder has a default data retention period (usually 10 days for states, longer for statistics). If you need very long-term state history for SQL queries, adjust the purge_keep_days in your recorder configuration. Statistics data is retained much longer by default.
• Optimize Database Performance: For large instances or frequent SQL queries, ensure your external database is properly configured and indexed.
• Backup Your Data: Regularly back up your Home Assistant configuration and, crucially, your external database. Data loss means loss of invaluable historical insights.

Conclusion

Moving beyond basic real-time control, Home Assistant's robust data capabilities empower you to become a true data scientist of your smart home. By leveraging utility meters for consumption tracking, understanding built-in statistics, and diving into custom SQL queries, you can unlock profound insights into your home's performance, optimize resource usage, and make informed decisions that lead to a more efficient and comfortable living environment. Start exploring your data today!