grafana - 使用 Influxdb 在 Grafana 中选择字符串值作为整数

Question

我想在时间序列图中将字符串值显示为 bool 值或整数值(数据来自家庭助理)

我有什么:

> 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

我想做这样的事情:

> 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

这可能吗？

主要目标是创建类似 this 的图形

编辑

我没能让它与 InfluxDb 一起工作。我将 homeassistant 的主数据库更改为 PostgreSQL。使用 PostgreSQL 作为 Grafana 中的数据源，我确实设法使用以下查询获得了所需的图形:

> 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;

Answer 1

不，这是不可能的(至少对于 InfluxQL)。您可以使用 Flux(InfluxDB 的下一代查询语言)，您可以在这里尝试 strings.countStr() function ，例如:

import "strings"

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