Barchart from InfluxDB request

Hello,

I am trying to create a bar chart from a query from my InfluxDB using the max values of several days. Unfortunately, I am not able to do this.

After converting using function node, it looks like this:

This is the flow:

[
    {
        "id": "b84ff6a83df2a030",
        "type": "inject",
        "z": "aff59ea3f84b5123",
        "name": "Select * from opendtu",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "query",
                "v": "SELECT max(\"value\") from mqtt_consumer WHERE (topic='open/dtu/114184019984/0/yieldday') and (time >= now() - 9d) Group by time(1d)",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 760,
        "y": 2540,
        "wires": [
            [
                "326357f7bb7c5fcb"
            ]
        ]
    },
    {
        "id": "326357f7bb7c5fcb",
        "type": "influxdb in",
        "z": "aff59ea3f84b5123",
        "influxdb": "69cee814.fcbdb8",
        "name": "OpenDTU",
        "query": "",
        "rawOutput": false,
        "precision": "",
        "retentionPolicy": "",
        "org": "organisation",
        "x": 1080,
        "y": 2540,
        "wires": [
            [
                "aa14700c096b3e10",
                "61752de52dbefa60"
            ]
        ]
    },
    {
        "id": "0cddcc96761b211a",
        "type": "ui_chart",
        "z": "aff59ea3f84b5123",
        "name": "",
        "group": "dd5579511605b911",
        "order": 2,
        "width": 0,
        "height": 0,
        "label": "chart",
        "chartType": "bar",
        "legend": "false",
        "xformat": "HH:mm:ss",
        "interpolate": "linear",
        "nodata": "",
        "dot": false,
        "ymin": "",
        "ymax": "",
        "removeOlder": 1,
        "removeOlderPoints": "",
        "removeOlderUnit": "3600",
        "cutout": 0,
        "useOneColor": false,
        "useUTC": false,
        "colors": [
            "#1f77b4",
            "#aec7e8",
            "#ff7f0e",
            "#2ca02c",
            "#98df8a",
            "#d62728",
            "#ff9896",
            "#9467bd",
            "#c5b0d5"
        ],
        "outputs": 1,
        "useDifferentColor": false,
        "className": "",
        "x": 1470,
        "y": 2540,
        "wires": [
            []
        ]
    },
    {
        "id": "61752de52dbefa60",
        "type": "debug",
        "z": "aff59ea3f84b5123",
        "name": "debug 1",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 1220,
        "y": 2620,
        "wires": []
    },
    {
        "id": "aa14700c096b3e10",
        "type": "function",
        "z": "aff59ea3f84b5123",
        "name": "function 9",
        "func": "// Assuming msg.payload contains your data array\nvar data = msg.payload.map(function(item) {\n    return { x: new Date(item.time), y: item.max };\n});\n\nmsg.payload = {\n    series: ['Max'],\n    data: [data]\n};\n\nreturn msg;",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 1300,
        "y": 2540,
        "wires": [
            [
                "0cddcc96761b211a",
                "9114a63405a9dd1c"
            ]
        ]
    },
    {
        "id": "9114a63405a9dd1c",
        "type": "debug",
        "z": "aff59ea3f84b5123",
        "name": "debug 2",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 1480,
        "y": 2620,
        "wires": []
    },
    {
        "id": "69cee814.fcbdb8",
        "type": "influxdb",
        "hostname": "192.168.178.10",
        "port": "8086",
        "protocol": "http",
        "database": "opendtu",
        "name": "",
        "usetls": false,
        "tls": "",
        "influxdbVersion": "1.x",
        "url": "http://localhost:8086",
        "rejectUnauthorized": false
    },
    {
        "id": "dd5579511605b911",
        "type": "ui_group",
        "name": "Group 1",
        "tab": "f1504e48476c784c",
        "order": 1,
        "disp": true,
        "width": 6
    },
    {
        "id": "f1504e48476c784c",
        "type": "ui_tab",
        "name": "Test",
        "icon": "dashboard",
        "order": 9,
        "disabled": false,
        "hidden": false
    }
]

What is my mistake?

Ralf

The x item must be a number so it should look like this:

{"x":1703953800000,"y":9}

Not knowing what the input data to your function node looks like makes it difficult to determine what your issue is. If the info I provided doesn't help, add a debug to the output of the influxdb-in node and copy and paste the output to a response.

There’s a great page in the docs (Working with messages : Node-RED) that will explain how to use the debug panel to find the right path to any data item.

Pay particular attention to the part about the buttons that appear under your mouse pointer when you over hover a debug message property in the sidebar.

BX00Cy7yHi

In order to make code readable and usable it is necessary to surround your code with three backticks (also known as a left quote or backquote ```)

``` 
   code goes here 
```

You can edit and correct your post by clicking the pencil :pencil2: icon.

See this post for more details - How to share code or flow json

A bar chart does not use X Y time series data. It should be of this format

[{
    "series": [ "X" ],
    "data": [ [5,6,9] ],
    "labels": [ "Jan", "Feb", "Mar" ]
}]

Your dates should be in the label and values in the data. Format the dates as you want them displayed.

More info on formats here node-red-dashboard/Charts.md at master · node-red/node-red-dashboard · GitHub as noted in the sidebar help text.

1 Like

Can you please give me a practical example of how I can transform this value?

[{"time":"2024-01-08T00:00:00.000Z","max":593},{"time":"2024-01-09T00:00:00.000Z","max":730},{"time":"2024-01-10T00:00:00.000Z","max":719},{"time":"2024-01-11T00:00:00.000Z","max":703},{"time":"2024-01-12T00:00:00.000Z","max":115},{"time":"2024-01-13T00:00:00.000Z","max":168},{"time":"2024-01-14T00:00:00.000Z","max":280},{"time":"2024-01-15T00:00:00.000Z","max":321},{"time":"2024-01-16T00:00:00.000Z","max":655},{"time":"2024-01-17T00:00:00.000Z","max":363}]

Try

[{"id":"b84ff6a83df2a030","type":"inject","z":"d1395164b4eec73e","name":"Select * from opendtu","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"time\":\"2024-01-08T00:00:00.000Z\",\"max\":593},{\"time\":\"2024-01-09T00:00:00.000Z\",\"max\":730},{\"time\":\"2024-01-10T00:00:00.000Z\",\"max\":719},{\"time\":\"2024-01-11T00:00:00.000Z\",\"max\":703},{\"time\":\"2024-01-12T00:00:00.000Z\",\"max\":115},{\"time\":\"2024-01-13T00:00:00.000Z\",\"max\":168},{\"time\":\"2024-01-14T00:00:00.000Z\",\"max\":280},{\"time\":\"2024-01-15T00:00:00.000Z\",\"max\":321},{\"time\":\"2024-01-16T00:00:00.000Z\",\"max\":655},{\"time\":\"2024-01-17T00:00:00.000Z\",\"max\":363}]","payloadType":"json","x":380,"y":3500,"wires":[["61752de52dbefa60","aa14700c096b3e10"]]},{"id":"61752de52dbefa60","type":"debug","z":"d1395164b4eec73e","name":"debug 1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":560,"y":3580,"wires":[]},{"id":"aa14700c096b3e10","type":"function","z":"d1395164b4eec73e","name":"function 9","func":"// Assuming msg.payload contains your data array\nlet data = []\nlet labels = []\nmsg.payload.forEach(function(item) {\n    labels.push(item.time);\n    data.push(item.max );\n});\n\nmsg.payload = [{\n    series: ['Max'],\n    data: [data],\n    labels : labels\n}];\n\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":580,"y":3500,"wires":[["0cddcc96761b211a","9114a63405a9dd1c"]]},{"id":"0cddcc96761b211a","type":"ui_chart","z":"d1395164b4eec73e","name":"","group":"dd5579511605b911","order":2,"width":0,"height":0,"label":"chart","chartType":"bar","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":810,"y":3500,"wires":[[]]},{"id":"9114a63405a9dd1c","type":"debug","z":"d1395164b4eec73e","name":"debug 2","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":820,"y":3580,"wires":[]},{"id":"dd5579511605b911","type":"ui_group","name":"Group 1","tab":"f1504e48476c784c","order":1,"disp":true,"width":6},{"id":"f1504e48476c784c","type":"ui_tab","name":"Test","icon":"dashboard","order":9,"disabled":false,"hidden":false}]
1 Like

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.