Grafana: how to display ON/OFF values from MySQL DB?

mysql
openhab2
grafana
Tags: #<Tag:0x00007fd316b96b30> #<Tag:0x00007fd316b969f0> #<Tag:0x00007fd316b968b0>

(John) #1

Setup:

  • Windows 10, 64bit
  • MqSQL DB
  • OH2.2
  • Grafana 4.6.3

Dear all

In OH I get for several items ON or OFF values which are stored in MySQL DB. Using the native OH charts these values will be transformed to 1 or 0 and can be displayed in OH charts.

Use case: room temperatur, chart shows:

  • current temperatur (goes up and down)
  • target temperatur (more or less static as long as desired temperature is not changed)
  • ON/OFF of heating system

In Grafana I didn’t figure out how to display these values.
How can I display ON/OFF values in a Grafana chart?

Is it possible to transform these ON/OFF values with a SQL query to 1 and 0?
image

My current query looks like that:

SELECT
  FLOOR(UNIX_TIMESTAMP(time)) as time_sec,
  value,
  'Heiz_DG_Zi1_Stellwert' as metric
FROM openhab.item266
WHERE $__timeFilter(time)
ORDER BY time ASC;

Thanks and regards
John


(Rob Pope) #2

A bit of ANSI-SQL is all you need

SELECT
FLOOR(UNIX_TIMESTAMP(time)) as time_sec,
CASE WHEN value = 'ON' THEN 1 ELSE 0 END as value,
'Heiz_DG_Zi1_Stellwert' as metric
FROM openhab.item266
WHERE $__timeFilter(time)
ORDER BY time ASC

I find the Discrete panel works well for showing this type of value too


image


(John) #3

@Rob_Pope you made my day :slight_smile:

The discrete panel is great - thanks a lot for your support.

Best regards
John


(Tobias) #4

Hello guys, first post here, so please be gentle :wink:

Is there a way to make the Discrete graph show the last value in the database for the whole timeframe? My persistence strategy for my switches is everyChange, so the output in discrete does not look so beautiful…

2018-07-11