Display of a graph within a period selected by Grafana

[dirkm]

Hello,

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:
table name:
iobroker
Tables:
datapoints (e.g. column: ID=44, name=mqtt.0.inverter.Hoymiles-HM-600.ch0.YieldDay and type=0)
sources
ts_bool
ts_counter
ts_number (e.g. column: ID=44, ts=1,680,771,015,900, val=488, ack=1, _from=3 and q=0
ts_string

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?

greeting

Dirk

[dirkm]

Hello,

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?

greeting

Dirk

[codlord]

You would normally specify a where clause on a database datetime field using the Grafana $__timeFilter value, for example:

where $__timeFilter(my_datetime_field_from_database)

[dirkm]

Hi,

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

greeting

dirk

[dirkm]

Hello,

i’m a little further

The query:

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?

greeting

Dirk