I wonder if anybody has ever tried that combination?
I moved my persistence from InfluxDB to JDBC-PostgreSQL and experienced problems to create any single graph. Then I discovered a huge conflict. OpenHAB is storing all values in two columns “time” and “value”. The problem is now, that Grafana uses the term “time” as column label. Let’s look at this query in Grafana:
SELECT
$__timeGroup("time",'1h',0),
sum(value) as value
FROM pv_ertrag_kwh_17
WHERE
$__timeFilter("time")
GROUP BY time
order by time
The generated SQL query looks like this:
SELECT
(extract(epoch from "time")/3600)::bigint*3600 AS time,
sum(value) as value
FROM pv_ertrag_kwh_17
WHERE
"time" BETWEEN '2018-05-22T20:41:06Z' AND '2018-05-24T20:41:06Z'
GROUP BY time
order by time
We have here the same name for two different things: 1. “time” as column name and 2. “AS time” as label name which is used in “GROUP BY time” and “ORDER by time”. When PostgreSQL executes this query it interprets “time” in GROUP BY and ORDER BY as column names and not label names. As the result PostgreSQL delivers wrong values and the graph is useless.
To solve this problem I created a view to rename the column “time”
create view pv_ertrag_kwh as select time as tcol, value from pv_ertrag_kwh_17;
Then I changed the query in Grafana to this one:
SELECT
$__timeGroup("tcol",'1h',0),
sum(value) as value
FROM pv_ertrag_kwh
WHERE
$__timeFilter("tcol")
GROUP BY time
order by time
Now everything is working correctly!
Again, am I alone using this combination?
Do you see any other workarounds?
… The best way would be to change the default column name to something very uncomon like “oh_time” !?