Select String value as Integer in Grafana with Influxdb

Question

I would like to show a string value as a boolean or int value in a timeseries graph (data coming from home-assistant)

What I have:

> select temperature, hvac_action_str from state where entity_id = 'my_entity_id'
name: state
time                temperature hvac_action_str
----                ----------- ---------------
1574189734402651904 20          idle
1574189824323437056 19          idle
1574190158807940864 19          heating
1574190462736049920 19          heating
1574190766798977024 19          idle

I would like to do something like this:

> select temperature, (hvac_action_str == 'heating' ? 1 : 0) as isHeating from state where entity_id = 'my_entity_id'
name: state
time                temperature isHeating
----                ----------- ---------------
1574189734402651904 20          0
1574189824323437056 19          0
1574190158807940864 19          1
1574190462736049920 19          1
1574190766798977024 19          0

Is this possible?

The main goal is to create a graph like this

Edit

I did not manage to get this to work with InfluxDb. I changed the main database of homeassistant to PostgreSQL. With PostgreSQL as datasource in Grafana I did manage to get the desired graph using following query:

> SELECT
  created AS "time", 
  CAST(attributes::json->>'current_temperature' AS float) AS "current_temperature",
  CAST(attributes::json->>'temperature' AS float) AS "temperature",
  CASE WHEN attributes::json->>'hvac_action' = 'heating' THEN CAST(attributes::json->>'current_temperature' AS float) ELSE 0 END AS "isHeating",
  state AS "state"
FROM states 
WHERE
  entity_id = 'my_thermostat' AND
  $__timeFilter(created)
ORDER BY created;

Did you ever get this working? Clearly we are both trying to recreate the home-assistant climate graphs in Grafana. — Johan Henkens, Apr 07 '20 at 00:46

score 0 · Answer 1 · answered Nov 30 '19 at 12:37

0

No, it is not possible (at least with InfluxQL). You may use Flux (next generation of query language for InfluxDB), where you can try strings.countStr() function, e.g.:

import "strings"

data
  |> map(fn: (r) => ({
      r with
      _value: strings.countStr(v: r.hvac_action_str, substr: "heating")
    })
  )

answered Nov 30 '19 at 12:37

Jan Garaj

25,598
3
38
59

I don't seem to get the Flux datasource plugin to work in Grafana just yet – sven Nov 30 '19 at 14:44
https://grafana.com/grafana/plugins/grafana-influxdb-flux-datasource – Jan Garaj Nov 30 '19 at 17:02

score 0 · Answer 2 · edited Oct 25 '20 at 15:55

I created a similar grafana query to read and display climate thermostat for Home Assistant with MySQL backend, if anyone is interested:

SELECT
  created AS "time",
  CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.hvac_action')) = 'heating' THEN 1 ELSE 0 END AS "isHeating",
  CAST(JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.current_temperature')) AS DECIMAL(3,1)) AS "current_temperature",
  CAST(JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.temperature')) AS DECIMAL(3,1)) AS "target_temperature"
FROM states
WHERE
  entity_id = "climate.main_thermostat" AND
  state <> "unavailable" AND
  $__timeFilter(created)
ORDER BY created

Home Assistant configuration.yaml to log to MySQL:

recorder:
  #purge_keep_days: 5
  db_url: mysql://dbusername:dbpassword@core-mariadb:3306/dbname?charset=utf8
  exclude:
    domains:
      - automation
      - updater
    entity_globs:
      - sensor.weather_*
    entities:
      - sun.sun # Don't record sun data
      - sensor.last_boot # Comes from 'systemmonitor' sensor platform
      - sensor.date
    event_types:
      - call_service # Don't record service calls

score 0 · Answer 3 · answered Nov 30 '20 at 10:56

I got this to work in Grafana/Influxdb by counting the amount of results with a given string value: SELECT count(result) AS "result" FROM /.*-.*/ WHERE $timeFilter AND result = 'FAIL' GROUP BY time(10s) fill(null)

So, for your purposes it would be something like: select count(hvac_action_str) from state where entity_id = 'my_entity_id' and hvac_action_str != 'idle' GROUP BY time(10s) fill(null)

Select String value as Integer in Grafana with Influxdb

Edit

3 Answers3