Value stop and repeat counting by 10000

Grafana Time Series Panel
1

Hello, I’m trying to integrate a value in (watt) from a mysql database, it works well so far. unfortunately the value stagnates from 10000. the value is then simply given as 10000 again and again in grafana. in the mqsql database it is the value already at 14766.869. I use this query

SELECT
UNIX_TIMESTAMP(TIMESTAMP) as time_sec,
CAST(VALUE AS DECIMAL(10, 6)) as value,
‘PM2.5’ as metric
FROM history
WHERE $__timeFilter(TIMESTAMP) AND DEVICE = 'PV_Summe ’ AND READING = ‘state’
ORDER BY TIMESTAMP ASC

what im doing wrong, can anyone help me.

2

Welcome

Can you please a screen shot of what you are seeing?>

3

Hi, Thx for your answer.
here are some screenshots of the situation. Sorry 4 my bad english… i try my very best :slight_smile:

mysql database
grafana query and tableview

Bildschirmfoto von 2022-11-16 19-52-07
Bildschirmfoto von 2022-11-16 19-52-071920×1080 452 KB

Bildschirmfoto von 2022-11-16 19-53-28
Bildschirmfoto von 2022-11-16 19-53-281920×1080 282 KB

1 Like
4

the increment stagnates at 10000, although the value in the mysql database becomes higher than 10000

5

what happens if you remove the tick

`

select cast(VALUE as decimal(10,6))

instead of

select cast(`VALUE` as decimal(10,6))

Also what data type is VALUE, I see some text in the data

image

6

select cast(VALUE as decimal(10,6))

I tried this adjustment and waited until the value was read again, but there was no change. These are my first attempts with grafana and mysql. Unfortunately I don’t understand what datatype value means, I import the value from an fhem instance into a mysql database. it is the yield of a solar system. The only thing i set is the datasource and the query code

7

Might want to read the following. How did you create and populate this table?

https://dev.mysql.com/doc/refman/8.0/en/data-types.html

1 Like
8

Do you mean the specification of the data type
DECIMAL, NUMERIC
? Unfortunately I don’t really have a clue about the subject, I generate the output via home automation software (Fhem) I use a tool for this called DB Log. I’m trying to adjust the output of this tool, but unfortunately without success so far. After all, isn’t the category value decisive? there is no text in this one. Text is only in the Event section. I will now try again to change the decimal place in the output from Fhem, maybe that will bring me success. Thank you in advance for the help

1 Like
9

Look at the AT (right below DUMMY) row and look at the VALUE column. I see Next: text unless I am blind

This indicates the data type for that column is not numeric nor decimal

10

Yes, you are absolutely right, but I don’t use this value with the included text at all. I use the value dummy in garafana, without AT, the value is 13268.561 there is no text here, the value with the text simply goes into the database, but is not used in grafana. I think the problem must be something else. Maybe the 3 digits after the decimal point are the problem. As soon as I have a solution I’ll let you know

1 Like
11

Doesn’t really matter if you use that row or not, it indicates that the column does not a properly configured data type. all of the values within it are string. how do we know? because one of the values shows as string.

12

ok, i try to exclude all data with text in value from my mysql database …

13

that won’t really solve your problem as long as the column data type is string. what needs to change is your database schema

14

Hey, I try a lot of things with no success. finally i change the VALUE AS DECIMAL to 6,0 . After that all is fine and shown up correctly.
thank you very much for your patience and help.

UNIX_TIMESTAMP(TIMESTAMP) as time_sec,
CAST(VALUE AS DECIMAL(6,0)) as value,
‘PM2.5’ as metric
FROM history
WHERE $__timeFilter(TIMESTAMP) AND DEVICE = ‘PV_Summe’ AND READING = ‘state’
ORDER BY TIMESTAMP ASC

1 Like