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)
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?
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;
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
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…