first of all, sorry for my bad english.
I have a problem with the display of a graph within a period selected by Grafana.
I run a small PV system. Here the data is entered in a MySQL database.
The structure of the database is as follows:
datapoints (e.g. column: ID=44, name=mqtt.0.inverter.Hoymiles-HM-600.ch0.YieldDay and type=0)
ts_number (e.g. column: ID=44, ts=1,680,771,015,900, val=488, ack=1, _from=3 and q=0
The graph is correct so far, but the displayed MAX value is not that of the selected period, but from the beginning.
With an InfluxDB, the code looks like this:
SELECT mean(“value”) FROM “mqtt.0.inverter.Hoymiles-HM-600.ch0.YieldDay” WHERE $timeFilter GROUP BY time($__interval) fill(null)
My MySQL code:
SELECT FROM_UNIXTIME(ts/1000),val as ‘Power’ FROM iobroker.ts_number WHERE id=44
GROUP BY is missing here. How do I do this?
maybe something different. The graph is generally displayed correctly. If a period (A) is selected, the graph adapts.
Only the max value (B) and mean value (C) are not taken from the selected period (A) but from all entire inside the database value val (Power).
How can I change this?
The max value (B) and mean value (C) should be calculated from the selected period (A).
Can someone help me?
You would normally specify a where clause on a database datetime field using the Grafana
$__timeFilter value, for example:
Thanks for your help. I’ve tried that too.
SELECT FROM_UNIXTIME(ts/1000) AS Zeit, val FROM iobroker.ts_number WHERE (id=55 and $__timeFilter(FROM_UNIXTIME(ts/1000)))
Unfortunately with moderate success
i’m a little further
SELECT FROM_UNIXTIME(ts/1000) AS Zeit, val FROM iobroker.ts_number WHERE (id=55 and FROM_UNIXTIME(ts/1000) > DATE_SUB(NOW(),interval 2 hour)) ORDER BY Zeit DESC;
works the way I want. Can someone help me there again as I do the part
replace “> DATE_SUB(NOW(),interval 2 hour)” with the selected time filter?