Hi!
I just set up Grafana on my Raspberry Pi. Works great so far. I have a MySQL database with the table “data” and the following layout:
id | channel_id | timestamp | value
1138679 12 1507834583002 1
1138678 16 1507834411524 1
1138677 12 1507834400549 1
1138676 10 1507834314441 22.312
1138675 8 1507834311876 23.687
1138674 10 1507834313608 50.75
My querry looks like this:
SELECT from_unixtime(timestamp/1000) as time_sec, value as value, channel_id as metric FROM data WHERE channel_id = 10
-> this creates ans Error. “Found row with no time value”
SELECT UNIX_TIMESTAMP(timestamp) as time_sec, value as value, channel_id as metric FROM data WHERE channel_id = 10
-> this creates a point at 1970
Does anybody has an idea?
Regards!
Karl
This should work:
SELECT timestamp/1000 as time_sec, value as value, channel_id as metric FROM data WHERE channel_id = 10
Hi Sven,
Thanks for your idea.
Unfortunately it also says “found row with no time value”.
Will the division by 1000 lead to a double value instead of an integer? Could this be the problem?
Regards!
Karl
Yes it wont be integer after the division but i dont think thats the reason i think you might have NULL values in your table.
You could try this query to exclude NULL values.
SELECT timestamp/1000 as time_sec, value as value, channel_id as metric FROM data WHERE channel_id = 10 AND timestamp IS NOT NULL
I checked my table. There are no Null values in. I also tried the querry in grafana, but no success.
Which time format grafana needs to display a data row? YYYY-MM-DD hh:mm:ss?
You were actually right about it needing to be integer. I thought it worked similar to postgres but that doesnt seem to be the case.
Can you try the following query:
SELECT cast(timestamp/1000 as signed integer) as time_sec, value as value, channel_id as metric FROM data WHERE channel_id = 10
Thanks for your new idea 
Grafana returns “no data points”.
If i use the querry in phpmyadmin I get a prettty nice result:
I have no idea where the problem is…
For mysql the time_sec column needs to be int and the value column needs to be float. Can you see in the query inspector if any data is returned? Does your selected time range match your data?
Hi Sven!
I upgraded php from Version 5 to 7 and suddently it worked like a charm!
thank you so much!
Karl
