SQL table content in Markdown card (E.g. Historical data)

[vovo500 (Volkmar)]

This is a short summary to show how to create a table with content from the SQL database in lovelace UI.

Example:

1. Create a entity in SQL integration
   Open Settings/Devices/Integration search for SQL integration.
   Create an entity containing a SQL statement. It must return a JSON object and a dummy state object.
   This is an example

SELECT  'OK' state, 
  CONCAT( '[',
    GROUP_CONCAT(
      JSON_OBJECT(
         'day',date_format(day, "%d %b %Y"), 
         'duration', duration, 
         'timing', timing, 
         'outertemperature', outertemperature
      ) ORDER BY day DESC LIMIT 10
  ),']') json 
FROM dynamic_prices.ha_heatpump_status_temperature 
WHERE state = 'Heating' ORDER BY day DESC ;

There are some hints:

• The state topic can have only 255 chars this is most often not enough.
  Solution: Provide a dummy state object ( E.g. ‘OK’ state)
  The JSON string is the second sql output. It is stored in an entity attribute. This has a large character limitation
• mariadb has a nice feature named “json_arrayagg”. It doesn’t work in Homeassistant. As a workaround, use the old fashioned way using CONCAT and GROUP_CONCAT
• It makes sense to disable automatic update to prevent permanent SQL traffic. The SQL Integration supports it.
• Ordering and limit can be specified in the GROUP_CONCAT

2. Create a markdown card with a content similar to this example

{%  set heatpumpstates = state_attr('sensor.ha_heatpump_status','json')|from_json %}
|Tag|     Dauer [min]|    Taktung|Aussen Temp.|
|:---:|---:|---:|---:|{% for state in heatpumpstates %}
|{{ state.day }}|{{ state.duration }}|{{ state.timing }}|{{ state.outertemperature }}|{% endfor %}

3. Optional: If you disabled SQL polling, create an automation to update the entity
   Example:

alias: Trigger Heatpump Report
description: Trigger Heatpump Report
triggers:
  - trigger: time
    at: "00:00"
actions:
  - target:
      entity_id: sensor.ha_heatpump_status
    action: homeassistant.update_entity
    data: {}
mode: single

I hope this is interesting to anyone.