Displaying points on chart from sqlite db

deedee · 26 June 2026 18:01

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

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

jbudd · 26 June 2026 19:48

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.

deedee · 26 June 2026 20:11

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

jbudd · 26 June 2026 20:25

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.

deedee · 26 June 2026 20:51

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

jbudd · 27 June 2026 00:10

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

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

gives me this on the dashboard

deedee · 27 June 2026 08:42

It still doesnt work for me , let me summarize my current config

sqlite node => fixed SQL
select datetime(unixtime, 'unixepoch') as dt, temp from readings
chart config

chartnode745×770 30.2 KB
Dashboard graph shows emptry graph with y axis labeled from 10-40 / x axis labels empty and no datapoints

jbudd · 27 June 2026 08:49

The word "key" against x and y in the chart config refer to the key:value pairs in the array of objects from the database.

Your timestamp key is "dt" not "unixtime"
(Because of the AS clause in select datetime(unixtime, 'unixepoch') AS dt)

deedee · 27 June 2026 10:37

Thank you for your help, it works finally, it felt like a struggle , i asked chatgpt for help but that was a waste of time.

I noticed that all the 1440 points now show in the graph , is that a feature of the chart node that it tries to fit all the points on the graph without a scroll bar?

TotallyInformation · 27 June 2026 11:39

You could also convert the seconds value into ms which echarts also seems to allow. At least from my recent experience starting to build some timeseries chart components.

jbudd · 27 June 2026 11:49

Good suggestion.
It just has to be a format which the js library recognises as date/time, not necessarily a string.

So that would be select unixtime * 1000, ... which @deedee originally had, though in a function node rather than the select statement.

deedee · 27 June 2026 11:58

Yes i had that originally but i couldnt get it to work

Topic		Replies	Views
Help with messaging on sqlite DB to dashboard chart Dashboard node-red-dashboard	10	594	10 May 2022
Node-RED dashboard chart node with timestamp Dashboard node-red-dashboard , database	2	885	29 January 2022
SQLITE data to Charts - Various Issues General database	12	2166	10 January 2022
Ui-chart, missing datapoints, sqlite database Dashboard dashboard-2	1	117	30 August 2024
Convert object from sqlite to use in line graph General database , function-node	1	227	26 February 2024

Displaying points on chart from sqlite db

Related topics