Data extraction from integration and visualization

[Joonaskaru]

Hello!

Any kind of tips and examples on how to use the data in meaningful way?

Weekly odometer times weekly avg fuel consumption to see average weekly fuel consumption.
I plan to create a template based on that. Goal is just to see how much fuel i burn in a year to see some average baseline cost.
I am planning to buy an fully electric car in future so it would be a good way to log and monitor my existing cars running cost.

Any examples on general how to make the lovelace designs? or waht designs you guys here use?
Any custom templates done by you and examples on how you use them?

[CM000n]

Personally, I have a slightly different conception for myself at home. Homeassistant and Lovelace are only for data collection and device control. I try to keep unnecessary graphical representations out of Homeassistant.

For analyses and graphical presentations I use SQL and Grafana. The Homeassistant sensor data I need for this I wrote with the help of the LTSSLTSS Custom Component into my Timescale / Postgres database.

[Joonaskaru]

Nice input. I am trying to achieve a dashboard like system where all connected devices are showing up. Goal is to have some interesting metrics also there. Long term data handling is a good idea. Your use case seems pretty solid and interesting. I'd have to look at it myself. Especially how to set up Grafana, LTSS and SQL in docerized environment.

[DurgNomis-drol]

Grafana is probably your best bet for visualizing the data. 😁

I am also interested in finding the best solution, so please post what you find out, and pictures are more then welcome (From both of you). I would love to maybe add a paragraph or two in the read about this at some point.

I also hope that the new long term statistics feature in HA will be useful for us at some point. Right now we can't use our sensors in the statistic graph card because of missing device classes.

[CM000n]

The creation of the corresponding Docker containers is in principle quite simple @Joonaskaru . The relevant sections of my docker-compose.yaml look like this, for example:

version: '3'

networks:
  proxy:
    external: true

  timescale:
    container_name: timescale
    restart: always
    image: timescale/timescaledb-postgis:latest-pg12
    networks:
      - proxy
    volumes:
      - /srv/dev-disk-by-id-ata-ST32000542AS_5XW2HFF9/data/docker_data/timescale_ha:/var/lib/postgresql/data
      - /etc/localtime:/etc/localtime:ro
    environment:
      - POSTGRES_USER=USER
      - POSTGRES_PASSWORD=PASSWORD
    ports:
      - "5442:5432"

  grafana:
    container_name: grafana
    restart: always
    image: grafana/grafana
    depends_on:
      - "timescale"
    networks:
      - proxy
    volumes:
      - /srv/dev-disk-by-id-ata-ST32000542AS_5XW2HFF9/data/docker_data/grafana:/var/lib/grafana
    environment:
      - GF_SECURITY_ALLOW_EMBEDDING=true
    ports:
      - "3000:3000"

  homeassistant:
    container_name: home-assistant
    image: homeassistant/home-assistant
    network_mode: host
    environment:
      TZ: Europe/Berlin
    depends_on:
      - "timescale"
    restart: always
    volumes:
      - /srv/dev-disk-by-id-ata-ST32000542AS_5XW2HFF9/data/docker_data/homeassistant:/config
      - /etc/localtime:/etc/localtime:ro
      - /dev/serial/by-id/:/dev/serial/by-id
      - /var/run/docker.sock:/var/run/docker.sock
    privileged: true

Afterwards, you can simply add LTSS via HACS as a custom component in Homeassistant and can follow the information for the initial LTSS setup. Once the setup is complete, you can simply select the sensors whose data is to be collected in your configuration.yaml. I prefer to select the sensors rather than just collect all sensors. This looks like this for me, for example:

ltss:
  db_url: !secret ltss_url
  include:
    domains:
      - climate
      - media_player
    entities:
      - device_tracker.corolla
      - sensor.corolla_odometer
      - sensor.corolla_fuel_tank
      - sensor.tank_corolla_l
      - ...some more sensors
     

When data is collected for the sensors, you can integrate the database into Grafana as a data source and the fun begins ;-)
I think you should always be purposeful to know what you want. Otherwise the danger of getting bogged down is too great, because you can do anything. I haven't done much yet either. At the moment it looks like this:

The graphic at the top left, for example, consists of two queries.
One to get the last speedometer reading of the day:

SELECT
    $__timeGroupAlias(i0.time,$__interval, previous)
    , 'ODO' AS metric
    , MAX(i0.state::FLOAT) AS value
FROM ltss AS i0
  WHERE $__timeFilter(i0.time) 
    AND i0.entity_id = 'sensor.corolla_odometer'
    AND i0.state NOT IN ('', 'unavailable')
GROUP BY 1,2
ORDER BY 1,2

...and one to determine the kilometres driven per day

SELECT
    $__timeGroupAlias(i1.time,$__interval)
    , 'Daily ODO' AS metric
    , MAX(i1.value)-MIN(i1.lag_value) AS value
FROM
    (
    SELECT
        i0.time
        , LAG((i0.state::FLOAT)) OVER(ORDER BY i0.time) AS lag_value
        , (i0.state::FLOAT) AS value
    FROM ltss AS i0
    WHERE $__timeFilter(i0.time) 
        AND i0.entity_id = 'sensor.corolla_odometer'
        AND i0.state NOT IN ('', 'unavailable')
    ) AS i1
GROUP BY 1,2
ORDER BY 1,2

Hope this helps a little as an input :-)

[CM000n]

Ahhh, and here is for example a query to determine the total fuel consumption for an arbitrary time period :-)

SELECT
  $__timeGroupAlias(i2.time,$__interval, previous)
  , 'Spritverbrauch' AS metric
  , ROUND((SUM(i2.value) OVER(ORDER BY i2.time)::NUMERIC),1)*-1 AS value
FROM 
   (
   SELECT
       i1.time
       , (i1.value-i1.lag_value)*0.4215 AS value
   FROM
    	(
    	SELECT
           i0.time
           , LAG((i0.state::FLOAT)) OVER(ORDER BY i0.time) AS lag_value
           , (i0.state::FLOAT) AS value
    	FROM ltss AS i0
    	WHERE $__timeFilter(i0.time) 
        	AND i0.entity_id = 'sensor.corolla_fuel_tank'
        	AND i0.state NOT IN ('', 'unavailable')
    	) AS i1
   ) AS i2
WHERE i2.value < 0
ORDER BY 1

[CM000n]

I experimented a bit today with the fuel consumption display. In the process, one gets quite nice insights. I can now display the consumption for any period of time. That also seems to be largely correct. The manually calculated annual consumption was largely consistent with what the MyT app tells me.
Only that I now get a nice trend and can see if the average consumption (blue line) changes over time, for example, by a different driving style. 🙂

[DurgNomis-drol]

This looks awesome!

[DurgNomis-drol]

@CM000n It would be awesome if you could write something up about how to do these things and add it to the readme. It does not need to be long, but i think others would love to use this. 😄 Maybe also link this thread.

[CM000n]

Of course, very much :-) However, I am now on vacation until 12 September and can not do it before. But I look afterwards what can be done ;-) best regards

[DurgNomis-drol]

No problem, enjoy your vacation! ⛱️

[desosav]

Hi @CM000n ,

I really appreciate what you've shared already and I was wondering if you could kindly share some additional sql queries that can help myself and others build some nice looking grafana visualizations. What I lack is the sql skills to configure this myself. So any input would be highly appreciated. Thanks!

[CM000n]

Hi @desosav, shure no problem.

The Grafana graphic above consists of three querys.

One to determine the distance traveled ("Strecke"), depending on the selected time window:

WITH t1 AS
        (
            SELECT
                time 
                , LAG(state::FLOAT) OVER (ORDER BY time) AS lag_value
                , state::FLOAT AS value 
            FROM ltss 
            WHERE $__timeFilter(time)
                AND entity_id = 'sensor.corolla_odometer'
                AND state NOT IN ('', 'unavailable')
        )

, t2 AS
        (
            SELECT
                $__timeGroupAlias(time,$__interval)
                , MAX(value)-MIN(lag_value) AS value
            FROM t1
            GROUP BY 1
            ORDER BY 1
        )

SELECT
    $__timeGroupAlias(t2.time,$__interval, previous)
    , 'Strecke' AS metric
    , SUM(t2.value) OVER(ORDER BY t2.time) AS value
FROM t2
ORDER BY 1

One to determine the total fuel consumption ("Spritverbrauch") over time.
Small hint here. Currently, it is not possible to determine consumption in liters via the integration. Only the tank level in % is displayed. So I multiplied the changes of the tank level with my tank volume. In the case of my Corolla this makes 100% * 0,4215 = 42,1 L

WITH t1 AS
        (
            SELECT
                time 
                , LAG(state::FLOAT) OVER (ORDER BY time) AS lag_value
                , state::FLOAT AS value 
            FROM ltss 
            WHERE $__timeFilter(time)
                AND entity_id = 'sensor.corolla_fuel_tank'
                AND state NOT IN ('', 'unavailable')
        )

, t2 AS
        (
            SELECT
                time
                , ((value - lag_value) * 0.4215) AS value
            FROM t1
        )

SELECT
    $__timeGroupAlias(t2.time,$__interval, previous)
    , 'Spritverbrauch' AS metric
    , (ROUND((SUM(t2.value) OVER (ORDER BY t2.time)::NUMERIC), 1) * -1) AS value
FROM t2
WHERE t2.value < 0
ORDER BY 1

And one to determine the average fuel consumption ("Ø Spritverbrauch")

WITH t1 AS
        (
            SELECT
                time 
                , LAG(state::FLOAT) OVER (ORDER BY time) AS lag_value
                , state::FLOAT AS value 
            FROM ltss 
            WHERE $__timeFilter(time)
                AND entity_id = 'sensor.corolla_odometer'
                AND state NOT IN ('', 'unavailable')
        )

, t2 AS
        (
            SELECT
                $__timeGroupAlias(time,$__interval, previous)
                , MAX(value)-MIN(lag_value) AS value
            FROM t1
            GROUP BY 1
            ORDER BY 1
        )

, t3 AS
        (
            SELECT
                time 
                , LAG(state::FLOAT) OVER (ORDER BY time) AS lag_value
                , state::FLOAT AS value 
            FROM ltss 
            WHERE $__timeFilter(time)
                AND entity_id = 'sensor.corolla_fuel_tank'
                AND state NOT IN ('', 'unavailable')
        )

, t4 AS
        (
            SELECT
                time
                , ((value - lag_value) * 0.4215) AS value
            FROM t3
        )

, t5 AS
        (
            SELECT
                $__timeGroupAlias(time,$__interval, previous)
                , 'Spritverbrauch' AS metric
                , SUM(t4.value) AS value
            FROM t4
            WHERE t4.value < 0
            GROUP BY 1
        )

SELECT
    $__timeGroupAlias(t2.time,$__interval, previous)
    , 'Ø Spritverbrauch' AS metric
    , ((SUM(t5.value) OVER (ORDER BY t2.time))*-1 / (SUM(t2.value) OVER(ORDER BY t2.time)))*100 AS value
FROM t2
    LEFT JOIN t5
    ON t2.time = t5.time 
ORDER BY 1

I hope this helps you a little ;-)
Feel free to ask if you have any further questions