Graph not showing values above 0

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’:wink:

— 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

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?

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…

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.

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 |
±-----------±------±-----------------+

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

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

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.

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

Marcus

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.

show columns from history;
±----------±-------------±-----±----±------------------±----------------------------+
| Field | Type | Null | Key | Default | Extra |
±----------±-------------±-----±----±------------------±----------------------------+
| TIMESTAMP | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| DEVICE | varchar(64) | YES | MUL | NULL | |
| TYPE | varchar(64) | YES | | NULL | |
| EVENT | varchar(512) | YES | | NULL | |
| READING | varchar(64) | YES | | NULL | |
| VALUE | varchar(128) | YES | | NULL | |
| UNIT | varchar(32) | YES | | NULL | |
±----------±-------------±-----±----±------------------±----------------------------+

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?

Hi,

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

Marcus

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
}
}
}
}

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

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

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

Michael

1 Like