Displaying points on chart from sqlite db

Dashboard
1

i have two columns unixtime in secs and temp ex 27.1 I read from a local database

I can see the data coming out of my convert function using debug
but i cant seem to have it display data from my sqlite3 database on the chart ( it has a 1440 records over 1 day last week every minute)

Only an empty graph is shown with time labels that correspend to the current timeframe not from what is in the database.

My system node-red 5.0.0 , dashboard2 last version

debug
debug328×507 6.18 KB

convert function :

[
    {
        "id": "c895817421a5b54b",
        "type": "function",
        "z": "4dfc04530322666a",
        "name": "Convert data",
        "func": "let rows = msg.payload.rows || msg.payload;\n\nmsg.payload = [{\n    series: \"Temp\",\n    data: rows.map(r => ({\n        x: r.unixtime * 1000,\n        y: r.temp\n    }))\n}];\n\nreturn msg;",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 670,
        "y": 220,
        "wires": [
            [
                "9a1f1aaae992eeca",
                "986640c8307a06ba"
            ]
        ]
    }
]

Below is the chart settings

[
    {
        "id": "9a1f1aaae992eeca",
        "type": "ui-chart",
        "z": "4dfc04530322666a",
        "group": "536adf9f94746080",
        "name": "",
        "label": "chart",
        "order": 1,
        "chartType": "line",
        "category": "topic",
        "categoryType": "msg",
        "xAxisLabel": "",
        "xAxisProperty": "payload.x",
        "xAxisPropertyType": "msg",
        "xAxisType": "linear",
        "xAxisFormat": "",
        "xAxisFormatType": "auto",
        "xmin": "",
        "xmax": "",
        "yAxisLabel": "",
        "yAxisProperty": "payload",
        "yAxisPropertyType": "msg",
        "ymin": "",
        "ymax": "",
        "bins": 10,
        "action": "append",
        "stackSeries": false,
        "pointShape": "circle",
        "pointRadius": 4,
        "showLegend": true,
        "removeOlder": "3",
        "removeOlderUnit": "86400",
        "removeOlderPoints": "",
        "colors": [
            "#0095ff",
            "#ff0000",
            "#ff7f0e",
            "#2ca02c",
            "#a347e1",
            "#d62728",
            "#ff9896",
            "#9467bd",
            "#c5b0d5"
        ],
        "textColor": [
            "#666666"
        ],
        "textColorDefault": true,
        "gridColor": [
            "#e5e5e5"
        ],
        "gridColorDefault": true,
        "width": 6,
        "height": 8,
        "className": "",
        "interpolation": "linear",
        "x": 850,
        "y": 220,
        "wires": [
            []
        ]
    },
    {
        "id": "536adf9f94746080",
        "type": "ui-group",
        "name": "Group 3",
        "page": "5f90f84eb3be396c",
        "width": 6,
        "height": 1,
        "order": 1,
        "showTitle": true,
        "className": "",
        "visible": "true",
        "disabled": "false",
        "groupType": "default"
    },
    {
        "id": "5f90f84eb3be396c",
        "type": "ui-page",
        "name": "Page 1",
        "ui": "95d4fa827ab4b0d4",
        "path": "/page1",
        "icon": "home",
        "layout": "grid",
        "theme": "1e378db9f1ad6c68",
        "breakpoints": [
            {
                "name": "Default",
                "px": "0",
                "cols": "3"
            },
            {
                "name": "Tablet",
                "px": "576",
                "cols": "6"
            },
            {
                "name": "Small Desktop",
                "px": "768",
                "cols": "9"
            },
            {
                "name": "Desktop",
                "px": "1024",
                "cols": "12"
            }
        ],
        "order": 1,
        "className": "",
        "visible": "true",
        "disabled": "false"
    },
    {
        "id": "95d4fa827ab4b0d4",
        "type": "ui-base",
        "name": "My Dashboard",
        "path": "/dashboard",
        "appIcon": "",
        "includeClientData": true,
        "acceptsClientConfig": [
            "ui-notification",
            "ui-control"
        ],
        "showPathInSidebar": false,
        "headerContent": "page",
        "navigationStyle": "default",
        "titleBarStyle": "default",
        "showReconnectNotification": true,
        "notificationDisplayTime": 1,
        "showDisconnectNotification": true,
        "allowInstall": false
    },
    {
        "id": "1e378db9f1ad6c68",
        "type": "ui-theme",
        "name": "Default Theme",
        "colors": {
            "surface": "#ffffff",
            "primary": "#0094CE",
            "bgPage": "#eeeeee",
            "groupBg": "#ffffff",
            "groupOutline": "#cccccc"
        },
        "sizes": {
            "density": "default",
            "pagePadding": "12px",
            "groupGap": "12px",
            "groupBorderRadius": "4px",
            "widgetGap": "12px"
        }
    },
    {
        "id": "abf878b67b930988",
        "type": "global-config",
        "env": [],
        "modules": {
            "@flowfuse/node-red-dashboard": "1.30.2"
        }
    }
]
2

Welcome to the forum @deedee

It would be helpful if we could see the data too. A picture of your flow isn't much use.

You can copy the value shown in your debug output with the buttons which appear when you hover over the data.
Post it here using the </> button so that we can copy and import the data.

Also please highlight just your chart node and export it (CTRL - e), then paste it here, again using the </> button.

3

Thank you , i have added the debug and chart config in my topic

4

You do not need to convert database output to x and y values for Dashboard 2.

You have not posted any data, just a picture of it (after your function has manipulated it?) so I can't be sure, but something like this might work.

image
image902×807 34.9 KB

5

Below is a json export of my sqlite table.
The timestamps have an interval of 1 minute / 1440 records in total

[
  {
    "_explicit_rowid_": 1,
    "unixtime": 1782384513,
    "temp": 17.71
  },
  {
    "_explicit_rowid_": 2,
    "unixtime": 1782384573,
    "temp": 17.82
  },
  {
    "_explicit_rowid_": 3,
    "unixtime": 1782384633,
    "temp": 18.06
  },
  {
    "_explicit_rowid_": 4,
    "unixtime": 1782384693,
    "temp": 18.25
  },
  {
    "_explicit_rowid_": 5,
    "unixtime": 1782384753,
    "temp": 17.84
  },
  {
    "_explicit_rowid_": 6,
    "unixtime": 1782384813,
    "temp": 18.11
  },
  {
    "_explicit_rowid_": 7,
    "unixtime": 1782384873,
    "temp": 18.09
  },
  {
    "_explicit_rowid_": 8,
    "unixtime": 1782384933,
    "temp": 18.1
  },
  {
    "_explicit_rowid_": 9,
    "unixtime": 1782384993,
    "temp": 17.99
  },
  {
    "_explicit_rowid_": 10,
    "unixtime": 1782385053,
    "temp": 17.77
  },
6

You can get a chart showing your data by setting x axis type Categorical, x key unixtime and y key temp:

image
image880×610 25 KB

But I doubt if a timestamp eg 1782384513 is what you really want to see on the x axis.

It makes more sense to tweek your SQL query to return a formatted date/time, something like this. Obviously adjust for your table name and columns, and check the parameters for datetime()

select id, datetime(unixtime, 'unixepoch') as dt, temp from temperature_readings

Then this chart node

image
image1299×687 31 KB

gives me this on the dashboard
image
image880×610 28.8 KB