Display / output Home Assistant data as a table

Charts are great, but a simple table view of historical data would be desirable for certain evaluations. Unfortunately, Home Assistant has very little to offer out of the box.

It is only possible to output any SQL queries from the database as a table in a Lovelance card in a roundabout way, which is the aim of this article: 

The example presented here includes data from five different entities that are read from the database, stored in an entity and output via a markdown card in Lovelace. In the markdown card, certain columns can be calculated based on the values of other columns: e.g. the column: "Delta" is calculated from "Lead" and "Return". For this example, I have used the custom integration sql_json . However, certain database values can also be displayed in a Lovelance card without the HACS integration sql_json:

☑ What is possible in HA with the standard SQL integration (Recommended for small amounts of data)

Using the existing SQL integration, it is possible to save the result for certain SQL queries in a separate sensor. Unfortunately, the SQL integration is designed to save only one value of an entity. In addition, the sensors have a limit of 255 characters at this point. However, for small amounts of data, several values can be stored in csv format in one entity, see: Home Assistant SQL - Integration. CSV data can then be output as a table via a markdown card. Here is a concrete example for the daily PV yield: SQL integration sensor, see:

Below is the SQL query used for a continuous entity (energy meter) from the States table:

[+] 
SELECT 
    GROUP_CONCAT(localdate || ":" || state, ",") AS val, 
    localdate 
FROM (
    SELECT 
        ROUND(Max(state) - Min(state) , 2) AS state,
        DATE(datetime(last_updated_ts, 'unixepoch', 'localtime')) AS localdate 
    FROM 
        states 
    WHERE 
        metadata_id = (
            SELECT metadata_id 
            FROM states_meta 
            WHERE entity_id = 'sensor.pv_panels_energy'
        ) 
        AND state NOT IN ("unknown", "", "unavailable") 
    GROUP BY 
        localdate 
    ORDER BY 
        localdate DESC
)

The sensor: sensor.pv_panels_energy must of course be adapted accordingly. To ensure that the query works, it should be tested in advance, see: 3 variants: SQLite database access - Home Assistant.

The query returns the following values:

2025-02-18:29.03,2025-02-17:16.4,2025-02-16:10.88,2025-02-15:24.32,2025-02-14:5.72,2025-02-13:24.19,2025-02-12:20.57,2025-02-11:24.02,2025-02-10:22.22,2025-02-09:29.59,2025-02-08:41.39

These values can then be displayed as a table in a markdown card:

Markdown:

[+] 
{% set data = states('sensor.pv_energy_daily').split(",")  %}
<table><tr>
  <th>date</th>
  <th>value</th>
</tr>
{% for i in range(0,data | count) %}
 <tr>
   <td  width=100>
     {{data[i].split(":")[0]  }}
   </td>
   <td align=right>
     {{ data[i].split(":")[1]  }} kWh
   </td>
 </tr>
{% endfor %}

🙋Solution for more than 255 characters: sql_json (Recommended for large amounts of data)

The limit of 255 characters does not apply to "state_attributes" of a database entry, which means that longer entries can also be created. A HACS integration is required to be able to save database queries as JSON:

The history table provides more data than the states table. Stored as a json in Configuration.yaml, the following query saves the data from the "sensor.pv_panesl_energy" sensor for the last 100 days:

[+] 
sensor:
  - platform: sql_json
    scan_interval: 86400
    queries:
      - name: "daily_pv_yield"
        query: >-
          SELECT json_group_array(
                    json_object(
                        'localdate', localdate, 
                        'state', state_diff
                    )
                ) AS json
          FROM (
              SELECT 
                  ROUND(MAX(state) - MIN(state), 2) AS state_diff,
                  DATE(datetime(created_ts, 'unixepoch', 'localtime')) AS localdate 
              FROM 
                  statistics 
              WHERE 
                  metadata_id = (
                      SELECT id 
                      FROM statistics_meta 
                      WHERE statistic_id = 'sensor.pv_panels_energy'
                  ) 
                  AND state NOT IN ("unknown", "", "unavailable") 
              GROUP BY 
                  localdate 
              ORDER BY 
                  localdate DESC
              LIMIT 100
          );
        value_template: '{{ value_json[0].state }}'
        column: json

So that the result of the query is not stored too often in the database, the "scan_interval" should not be set too low. The query can also be triggered via the GUI in order to obtain up-to-date data:

Refresh values

If required, the query can also be executed via a script or automation:

Displaying the data in a Markdown map

The easiest way to display the data in tabular form is to use a Markdown map:

{% set data = state_attr('sensor.daily_pv_yield','json')  %}
<table><tr>
  <th>Date</th>
  <th align=right>value</th>
</tr>
{% for i in range(0,data | count)%}
 <tr>
   <td align=right>
     {{data[i].localdate  }}
   </td>
   <td align=right width=100>
     {{ '{:.2f}'.format(data[i].state | round(2))  }} 
   </td>
 </tr>
{% endfor %}

Combine certain entities in a query

This variant can be used to combine the data from several sensors; here is the corresponding query for the example presented at the beginning of this article:

[+] 
      - name: "daily_test"
        query: >-
          SELECT  json_group_array(
                  json_object(
                      'localdate', flowmeter_sum.localdate, 
                      'flowmeter_sum', flowmeter_sum.state_diff,
                      'aussen_temperatur_mean', aussen_temperature.state_mean,
                      'flowmeter', flowmeter.state,
                      'heating_vorlauf', heating_vorlauf.state_min,
                      'heating_ruecklauf', heating_ruecklauf.state_min
                  )
              ) AS json
          FROM (
              SELECT 
                  ROUND(MAX(state) - MIN(state), 2) AS state_diff,
                  DATE(datetime(created_ts, 'unixepoch', 'localtime')) AS localdate 
              FROM 
                  statistics 
              WHERE 
                  metadata_id = (
                      SELECT id 
                      FROM statistics_meta 
                      WHERE statistic_id = 'sensor.flowmeter_sum'
                  ) 
                  AND state NOT IN ("unknown", "", "unavailable")                                      
                  AND DATE(datetime(created_ts, 'unixepoch', 'localtime')) < DATE('now')
              GROUP BY 
                  localdate 
              ORDER BY localdate DESC
          ) flowmeter_sum
          LEFT JOIN (
              SELECT 
                  ROUND(AVG(mean), 2) AS state_mean,
                  DATE(datetime(created_ts, 'unixepoch', 'localtime')) AS localdate 
              FROM 
                  statistics 
              WHERE 
                  metadata_id = (
                      SELECT id 
                      FROM statistics_meta 
                      WHERE statistic_id = 'sensor.aussen_temperature'
                  ) 
                  AND mean NOT IN ("unknown", "", "unavailable") 
              GROUP BY 
                  localdate 
          ) aussen_temperature ON flowmeter_sum.localdate = aussen_temperature.localdate
          LEFT JOIN (
              SELECT 
                  ROUND(max(mean), 2) AS state,
                  DATE(datetime(created_ts, 'unixepoch', 'localtime')) AS localdate 
              FROM 
                  statistics 
              WHERE 
                  metadata_id = (
                      SELECT id 
                      FROM statistics_meta 
                      WHERE statistic_id = 'sensor.flowmeter'
                  ) 
                  AND max NOT IN ("unknown", "", "unavailable") 
              GROUP BY 
                  localdate 
          ) flowmeter ON flowmeter.localdate = aussen_temperature.localdate
          LEFT JOIN (
              SELECT 
                  ROUND(min(min), 2) AS state_min,
                  DATE(datetime(created_ts, 'unixepoch', 'localtime')) AS localdate 
              FROM 
                  statistics 
              WHERE 
                  metadata_id = (
                      SELECT id 
                      FROM statistics_meta 
                      WHERE statistic_id = 'sensor.heating_ruecklauf'
                  ) 
                  AND max NOT IN ("unknown", "", "unavailable") 
              GROUP BY 
                  localdate 
          ) heating_ruecklauf ON flowmeter.localdate = heating_ruecklauf.localdate
          LEFT JOIN (
              SELECT 
                  ROUND(min(min), 2) AS state_min,
                  DATE(datetime(created_ts, 'unixepoch', 'localtime')) AS localdate 
              FROM 
                  statistics 
              WHERE 
                  metadata_id = (
                      SELECT id 
                      FROM statistics_meta 
                      WHERE statistic_id = 'sensor.heating_vorlauf'
                  ) 
                  AND max NOT IN ("unknown", "", "unavailable") 
              GROUP BY 
                  localdate 
          ) heating_vorlauf ON heating_ruecklauf.localdate = heating_vorlauf.localdate;
        value_template: '{{ value_json[0].state }}'
        column: json

Markdown card content:

[+] 
{% set data = state_attr('sensor.daily_heating','json')  %}
<table><tr>
  <th>Datum</th>
  <th align=right>Flowmeter SUM</th>
  <th align=right>AVG Aussen</th>
  <th align=right>Flowmeter</th>
  <th align=right>Delta</th>
  <th align=right>Vorlauf</th>
  <th align=right>Rücklauf</th>
</tr>
{% for i in range(0,data | count)%}
 <tr>
   <td align=right>
     {{data[i].localdate  }}
   </td>
   <td align=right>
     {{ '{:.2f}'.format(data[i].flowmeter_sum | round(2))  }} m³
   </td>
   <td align=right>
     {{ '{:.2f}'.format(data[i].aussen_temperatur_mean | round(2, 'floor')) }} °C
   </td>
   <td align=right>
     {{ '{:.2f}'.format(data[i].flowmeter| round(2, 'floor'))}} m³/h
   </td>
   <td align=right >
     {{ '{:.1f}'.format((data[i].heating_vorlauf - data[i].heating_ruecklauf) | round(1, 'floor'))  }} °C
   </td>   
   <td align=right>
     {{ '{:.1f}'.format(data[i].heating_vorlauf | round(1, 'floor')) }} °C
   </td>   
   <td align=right>
     {{ '{:.1f}'.format(data[i].heating_ruecklauf | round(1, 'floor')) }} °C
   </td>
 </tr>
{% endfor %}

Markdown card limit

Displaying data with more than 262144 characters overloads the Markdown card and leads to an error:

(Template output exceeded maximum size of 262144 characters)

One way to circumvent the Markdown Card limit is a helper for displaying pages: "input_number.heating_pagination"

The helper can then be used in the Markdown card. The variable "numlist" limits the number of entries per page:

[+] 
type: markdown
content: |
  {% set data = state_attr('sensor.daily_heating','json')  %}
  {% set pagination = states('input_number.heating_pagination')  | int(0)   %}
  {% set numlist = 500  %}
  {% set start = (pagination * numlist) - numlist %}
  {% set end = start + numlist %}
  {% if(end > (data | count)) %}
  {% set end = data | count %}
  {% endif %}

  <table><tr>
    <th>Datum</th>
    <th align=right>Flowmeter SUM</th>
    <th align=right>AVG Aussen</th>
    <th align=right>Flowmeter</th>
    <th align=right>Runtime</th>
    <th align=right>Delta</th>
    <th align=right>Heizleistung</th>
    <th align=right>Vorlauf</th>
    <th align=right>Rücklauf</th>
  </tr>{#data | count#}
  {% for i in range(start, end)%}
   <tr>
     <td align=right>
       {{data[i].localdate  }}
     </td>
     <td align=right>
       {% if (data[i].flowmeter | float(0) > 0.5) %}{{ '{:.2f}'.format(data[i].flowmeter_sum | float(0) | round(2))  }}{% else %}-{% endif %} m³
     </td>
     <td align=right>
       {{data[i].aussen_temperatur_mean | float("n/a")}} °C
     </td>
     <td align=right>
       {{ '{:.2f}'.format(data[i].flowmeter| float(0) | round(2, 'floor'))}} m³/h
     </td>   
     <td align=right>
       {% if (data[i].flowmeter | float(0) > 0.5) %}{{ '{:.2f}'.format(data[i].flowmeter_sum | float(0) / data[i].flowmeter | float(0) | round(2, 'floor'))}}{% else %}-{% endif %}h
     </td>
     <td align=right >
       {% if (data[i].flowmeter | float(0) > 0.5) %}{{ '{:.1f}'.format((data[i].heating_vorlauf | float(0)  - (data[i].heating_ruecklauf) | float(0)) | round(1, 'floor'))  }}{% else %}-{% endif %} °C
     </td>     <td align=right >
       {% if (data[i].flowmeter | float(0) > 0.5) %}{{ '{:.1f}'.format(((data[i].heating_vorlauf | float(0)  - (data[i].heating_ruecklauf) | float(0))) * 1.163 * data[i].flowmeter_sum | float(0) | round(2, 'floor'))  }}{% else %}-{% endif %} kWh
     </td>    
     <td align=right>
       {{ '{:.1f}'.format(data[i].heating_vorlauf | float(0) | round(2, 'floor')) }} °C
     </td>   
     <td align=right>
       {{ '{:.1f}'.format(data[i].heating_ruecklauf | float(0) | round(2, 'floor')) }} °C
     </td>

   </tr>
  {% endfor %}
grid_options:
  columns: full
text_only: true
card_mod:
  style:
    ha-markdown:
      $:
        ha-markdown-element: |
          table {
            width: 100%;
            padding: 10px;
            margin: -20px!important;
          }
          th, td {
            padding: 4px;                      
            overflow: hidden; 
            text-overflow: ellipsis;
            white-space: nowrap;
          }
          tr:nth-child(even) {
            background-color: var(--secondary-background-color);
          }

The example shows the "Code Editor View (YAML)" and uses the HACS integration "Card_Mod" and thus implemented CSS styles:

By adding the helper for the current page, you can switch between pages in steps of 500.

alternative display: HACS integration: Flex Table

The Flex Table integration is a little easier to set up, but less flexible:

For simple tables ok, but at the latest when linking or calculating certain columns, the Flex Table Card reaches its limits, so I would prefer the Markdown Card to the Flex-table Card.

ⓘ Call up data directly, Plotly-Graph-Table

The table view of Plotly-Graph should not go unmentioned at this point. Originally designed for displaying charts, Plotly-Graph can also display historical data as a table: Directly and without using a query in advance.

[+] 
type: custom:plotly-graph
hours_to_show: 999
entities:
  - entity: sensor.pv_panels_energy
    type: table
    period:
      "0": day
    statistic: state
    columnwidth:
      - 16
      - 20
    header:
      values:
        - Date
        - value
      fill:
        color: $ex css_vars["primary-color"]
      font:
        color: $ex css_vars["primary-text-color"]
    filters:
      - delta
    cells:
      values:
        - |
          $ex xs.toReversed().map(x=>
            new Intl.DateTimeFormat('de-DE', {
              day: '2-digit',
              month: '2-digit',
              year: '2-digit'
            }).format(x))
        - $ex ys.toReversed()
      align:
        - center
        - left
      fill:
        color: $ex css_vars["card-background-color"]

Example 2: several columns with different sensors:

[+] 
type: custom:plotly-graph
hours_to_show: 999999
entities:
  - entity: sensor.heating_water_energy
    type: table
    period:
      "0": day
    statistic: sum
    filters:
      - delta
      - store_var: water
  - entity: sensor.flowmeter_sum
    type: table
    period:
      "0": day
    statistic: sum
    columnwidth:
      - 16
      - 10
      - 10
    header:
      values:
        - Date
        - Flowmeter
        - Water
      fill:
        color: $ex css_vars["primary-color"]
      font:
        color: $ex css_vars["primary-text-color"]
    filters:
      - delta
    cells:
      values:
        - |
          $ex xs.toReversed().map(x=>
            new Intl.DateTimeFormat('de-DE', {
              day: '2-digit',
              month: '2-digit',
              year: '2-digit'
            }).format(x))
        - $ex ys.toReversed().map(x=> x.toFixed(2))
        - $ex vars.water.ys.map(x=> x.toFixed(2))
      align:
        - center
        - left
      fill:
        color: $ex css_vars["card-background-color"]
grid_options:
  columns: full
  rows: 12

The table view in Plotly: works, but is definitely not its core competence. Plotly is neither visually convincing nor easy to use (scrolling behavior). The data cannot be exported or copied to the clipboard. For these reasons, I cannot recommend Plotly for displaying tables.

Conclusion

Home Assistant has its strengths in displaying current values and has great data visualization capabilities. However, certain other solutions offer more for storing and displaying historical data. Not only when visualizing charts, but also when displaying tables, Home Assistant could take an example from other visualization solutions such as Grafana.

positive Bewertung({{pro_count}})
Rate Post:
{{percentage}} % positive
negative Bewertung({{con_count}})

THANK YOU for your review!

Questions / Comments


 
By continuing to browse the site, you agree to our use of cookies. More Details