Graph not showing values above 0

moneybox · March 3, 2018, 10:01am

Hi all,

I`m logging my temperature values to a MariaDB and want to visualise these data. Unfortuanetly only values below 0 are shown in the graph, the positive values are missing.

I checked my values and there are positive values in place (select DATE_FORMAT(timestamp, ‘%Y-%m-%d %H:%i:%s’),DEVICE,READING,VALUE FROM history WHERE timestamp BETWEEN NOW() - INTERVAL 30 DAY and NOW() and DEVICE=‘Aussentemperatur’

— snip —
±--------------------------------------------±-----------------±------------±------+
| DATE_FORMAT(timestamp, ‘%Y-%m-%d %H:%i:%s’) | DEVICE | READING | VALUE |
±--------------------------------------------±-----------------±------------±------+
| 2018-02-24 10:08:42 | Aussentemperatur | temperature | -2.38 |
| 2018-02-24 10:23:42 | Aussentemperatur | temperature | -2.12 |
| 2018-02-24 10:38:42 | Aussentemperatur | temperature | -1.62 |
| 2018-02-24 10:53:42 | Aussentemperatur | temperature | -1.38 |
| 2018-02-24 11:08:42 | Aussentemperatur | temperature | -0.88 |
| 2018-02-24 11:23:42 | Aussentemperatur | temperature | -0.62 |
| 2018-02-24 11:38:42 | Aussentemperatur | temperature | -0.44 |
| 2018-02-24 11:53:42 | Aussentemperatur | temperature | 0.06 |
| 2018-02-24 12:08:42 | Aussentemperatur | temperature | 0.31 |
| 2018-02-24 12:33:28 | Aussentemperatur | temperature | 0.81 |
| 2018-02-24 12:48:28 | Aussentemperatur | temperature | 1.06 |
| 2018-02-24 13:33:28 | Aussentemperatur | temperature | 1.81 |
| 2018-02-24 14:03:28 | Aussentemperatur | temperature | 2.06 |
| 2018-02-24 14:18:28 | Aussentemperatur | temperature | 2.25 |
| 2018-02-24 14:48:28 | Aussentemperatur | temperature | 1.81 |
| 2018-02-24 15:03:28 | Aussentemperatur | temperature | 1.31 |
| 2018-02-24 15:18:28 | Aussentemperatur | temperature | 1.56 |
| 2018-02-24 15:48:28 | Aussentemperatur | temperature | 1.06 |
| 2018-02-24 16:18:28 | Aussentemperatur | temperature | 0.81 |
| 2018-02-24 16:48:28 | Aussentemperatur | temperature | 0.56 |
| 2018-02-24 17:33:28 | Aussentemperatur | temperature | 0.31 |
| 2018-02-24 17:48:28 | Aussentemperatur | temperature | -0.19 |
| 2018-02-24 18:03:28 | Aussentemperatur | temperature | -0.44 |
| 2018-02-24 18:18:28 | Aussentemperatur | temperature | -0.62 |
| 2018-02-24 18:48:28 | Aussentemperatur | temperature | -0.88 |
| 2018-02-24 19:03:28 | Aussentemperatur | temperature | -1.12 |
| 2018-02-24 19:18:28 | Aussentemperatur | temperature | -1.38 |

Here my json:

{
“aliasColors”: {},
“bars”: false,
“dashLength”: 10,
“dashes”: false,
“datasource”: “FHEM”,
“fill”: 1,
“hideTimeOverride”: false,
“id”: 1,
“legend”: {
“alignAsTable”: true,
“avg”: true,
“current”: true,
“hideEmpty”: false,
“hideZero”: false,
“max”: true,
“min”: true,
“show”: true,
“total”: false,
“values”: true
},
“lines”: true,
“linewidth”: 1,
“links”: ,
“minSpan”: null,
“nullPointMode”: “null”,
“percentage”: false,
“pointradius”: 5,
“points”: false,
“renderer”: “flot”,
“repeat”: null,
“seriesOverrides”: ,
“spaceLength”: 10,
“span”: 12,
“stack”: false,
“steppedLine”: false,
“targets”: [
{
“alias”: “”,
“format”: “time_series”,
“hide”: false,
“rawSql”: “SELECT\nUNIX_TIMESTAMP(TIMESTAMP) as time_sec,\nVALUE as value, "Außentemperatur" as metric \nFROM history WHERE READING="temperature" AND DEVICE="Aussentemperatur" AND $__timeFilter( TIMESTAMP )\n”,
“refId”: “A”
}
],
“thresholds”: ,
“timeFrom”: null,
“timeShift”: null,
“title”: “Außentemperatur”,
“tooltip”: {
“shared”: true,
“sort”: 0,
“value_type”: “individual”
},
“transparent”: true,
“type”: “graph”,
“xaxis”: {
“buckets”: null,
“mode”: “time”,
“name”: null,
“show”: true,
“values”:
},
“yaxes”: [
{
“decimals”: null,
“format”: “celsius”,
“label”: “”,
“logBase”: 1,
“max”: null,
“min”: null,
“show”: true
},
{
“format”: “short”,
“label”: null,
“logBase”: 1,
“max”: null,
“min”: null,
“show”: false
}
]
}

Hopefully someone can give me a hint how I can troubleshoot/resolve this.

Michael

svenklemm · March 3, 2018, 2:52pm

Does your graph have a fixed max for the y value? Are the positive points included if you check the resultset in the query inspector?

moneybox · March 3, 2018, 3:45pm

Hi Sven,

I haven’t defined any max values but I checked the query inspector for a specific time range and there are ‘null’ values where I get positive values in my SQL query.

          [
            -0.62,
            1519467822000
          ],
          [
            -0.44,
            1519468722000
          ],
          [
            null,
            1519469622000
          ],
          [
            null,
            1519470522000
          ],
          [
            null,
            1519472008000
          ],

So the output makes sense, but I don’t know why the values are ‘null’.
Seems a little bit strange for me…

svenklemm · March 3, 2018, 3:55pm

Hmm this looks weird what version of grafana are you running? Can you double check that your query works. Grafana has a button to show the generated sql it sends to the database and see if it has the right values if you run it directly.

moneybox · March 3, 2018, 4:22pm

I’m using Grafana v4.6.3 (commit: 7a06a47) an a RPI 3 with MariaDB 10.1.23-MariaDB-9+deb9u1

I picked the query from the query inspector and ran it against my database. The output looks good.

SELECT UNIX_TIMESTAMP(TIMESTAMP) as time_sec, VALUE as value, “Außentemperatur” as metric FROM history WHERE READING=“temperature” AND DEVICE=“Aussentemperatur” AND TIMESTAMP >= FROM_UNIXTIME(1519463187) AND TIMESTAMP <= FROM_UNIXTIME(1519497155);
±-----------±------±-----------------+
| time_sec | value | metric |
±-----------±------±-----------------+
| 1519463322 | -2.38 | Außentemperatur |
| 1519464222 | -2.12 | Außentemperatur |
| 1519465122 | -1.62 | Außentemperatur |
| 1519466022 | -1.38 | Außentemperatur |
| 1519466922 | -0.88 | Außentemperatur |
| 1519467822 | -0.62 | Außentemperatur |
| 1519468722 | -0.44 | Außentemperatur |
| 1519469622 | 0.06 | Außentemperatur |
| 1519470522 | 0.31 | Außentemperatur |
| 1519472008 | 0.81 | Außentemperatur |
| 1519472908 | 1.06 | Außentemperatur |
| 1519475608 | 1.81 | Außentemperatur |
| 1519477408 | 2.06 | Außentemperatur |
| 1519478308 | 2.25 | Außentemperatur |
| 1519480108 | 1.81 | Außentemperatur |
| 1519481008 | 1.31 | Außentemperatur |
| 1519481908 | 1.56 | Außentemperatur |
| 1519483708 | 1.06 | Außentemperatur |
| 1519485508 | 0.81 | Außentemperatur |
| 1519487308 | 0.56 | Außentemperatur |
| 1519490008 | 0.31 | Außentemperatur |
| 1519490908 | -0.19 | Außentemperatur |
| 1519491808 | -0.44 | Außentemperatur |
| 1519492708 | -0.62 | Außentemperatur |
| 1519494508 | -0.88 | Außentemperatur |
| 1519495408 | -1.12 | Außentemperatur |
| 1519496308 | -1.38 | Außentemperatur |
±-----------±------±-----------------+

mefraimsson · March 3, 2018, 5:12pm

We’ve done some changes to handling of datatypes in the latest v5.0 release which hopefully solves your problem. I would suggest you to try that out.

Marcus

moneybox · March 3, 2018, 6:50pm

Ok, I’ll try this and will give feedback.

moneybox · March 4, 2018, 11:00am

I just upgraded to Grafana v5.0.0 (commit: af6e283) but the behaviour ist the same, I´ll get ‘null’ values instead of the positive values from database.

mefraimsson · March 4, 2018, 1:04pm

Okay. So what kind of data type has the column value in your table history?

Marcus

moneybox · March 4, 2018, 1:27pm

Hi Marcus,

I’ve checked and it seems that the column VALUE is a VARCHAR(128). I’m storing different values in this table like float and string so that makes sense for me.

When I’m using the CAST function as below the values reported in the SQL query with a decimal point.

SELECT UNIX_TIMESTAMP(TIMESTAMP) as time_sec, CAST(VALUE as DECIMAL(4,2)) as value, “Außentemperatur” as metric FROM history WHERE READING=“temperature” AND DEVICE=“Aussentemperatur” AND TIMESTAMP >= FROM_UNIXTIME(1519466922) AND TIMESTAMP <= FROM_UNIXTIME(1519495408);
±-----------±------±-----------------+
| time_sec | value | metric |
±-----------±------±-----------------+
| 1519466922 | -0.88 | Außentemperatur |
| 1519467822 | -0.62 | Außentemperatur |
| 1519468722 | -0.44 | Außentemperatur |
| 1519469622 | 0.06 | Außentemperatur |
| 1519470522 | 0.31 | Außentemperatur |
| 1519472008 | 0.81 | Außentemperatur |
| 1519472908 | 1.06 | Außentemperatur |
| 1519475608 | 1.81 | Außentemperatur |
| 1519477408 | 2.06 | Außentemperatur |
| 1519478308 | 2.25 | Außentemperatur |
| 1519480108 | 1.81 | Außentemperatur |
| 1519481008 | 1.31 | Außentemperatur |
| 1519481908 | 1.56 | Außentemperatur |
| 1519483708 | 1.06 | Außentemperatur |
| 1519485508 | 0.81 | Außentemperatur |
| 1519487308 | 0.56 | Außentemperatur |
| 1519490008 | 0.31 | Außentemperatur |
| 1519490908 | -0.19 | Außentemperatur |
| 1519491808 | -0.44 | Außentemperatur |
| 1519492708 | -0.62 | Außentemperatur |
| 1519494508 | -0.88 | Außentemperatur |
| 1519495408 | -1.12 | Außentemperatur |
±-----------±------±-----------------+

Unfortunately my graph isn’t showing the values with a decimal point.

Can you provide me a hint how to change the graph so I’m able to see the values with a decimal point?

mefraimsson · March 4, 2018, 1:33pm

Hi,

Please change Decimals settings under Axes tab and under Legend tab.

Marcus

moneybox · March 4, 2018, 1:55pm

I already tried that, but with no success.

Here an example from the query inspector:

{
“xhrStatus”: “complete”,
“request”: {
“method”: “POST”,
“url”: “api/tsdb/query”,
“data”: {
“from”: “1519467792630”,
“to”: “1519493913653”,
“queries”: [
{
“refId”: “A”,
“intervalMs”: 15000,
“maxDataPoints”: 1616,
“datasourceId”: 2,
“rawSql”: “SELECT\nUNIX_TIMESTAMP(TIMESTAMP) as time_sec,\nCAST(VALUE as DECIMAL(4, 2)) as value, "Außentemperatur" as metric \nFROM history WHERE READING="temperature" AND DEVICE="Aussentemperatur" AND $__timeFilter( TIMESTAMP )\n”,
“format”: “time_series”
}
]
}
},
“response”: {
“results”: {
“A”: {
“refId”: “A”,
“meta”: {
“rowCount”: 19,
“sql”: “SELECT\nUNIX_TIMESTAMP(TIMESTAMP) as time_sec,\nCAST(VALUE as DECIMAL(4, 2)) as value, "Außentemperatur" as metric \nFROM history WHERE READING="temperature" AND DEVICE="Aussentemperatur" AND TIMESTAMP >= FROM_UNIXTIME(1519467792) AND TIMESTAMP <= FROM_UNIXTIME(1519493913)\n”
},
“series”: [
{
“name”: “Außentemperatur”,
“points”: [
[
-0.62,
1519467822000
],
[
-0.44,
1519468722000
],
[
0.06,
1519469622000
],
[
0.31,
1519470522000
],
[
0.81,
1519472008000
],
[
1.06,
1519472908000
],
[
1.81,
1519475608000
],
[
2.06,
1519477408000
],
[
2.25,
1519478308000
],
[
1.81,
1519480108000
],
[
1.31,
1519481008000
],
[
1.56,
1519481908000
],
[
1.06,
1519483708000
],
[
0.81,
1519485508000
],
[
0.56,
1519487308000
],
[
0.31,
1519490008000
],
[
-0.19,
1519490908000
],
[
-0.44,
1519491808000
],
[
-0.62,
1519492708000
]
]
}
],
“tables”: null
}
}
}
}

mefraimsson · March 4, 2018, 2:17pm

There’s a Decimals setting under the legend tab as well

moneybox · March 4, 2018, 2:31pm

Ahh, sorry, I misread your post and skipped the legend tab. Now it’s working like a charm

Many thank for all your support and finally I learned very much!

Michael