Can ui_chart import data from a database?

Dashboard
1

Hi,

I have a sqlite database with gas and electric multiple readings for each day.

I now want to display these in a graph,can ui_chart somehow import these and display them ?
If possible a datepicker would be use in the future.

Any thoughts or ideas welcome !

Thanks

2

The ui chart can't import data, but you can get the data out of the db and send it to the chart. If you look at the Info tab for the chart node you will find a link to more information which tells you the format of the data you must give it to draw a chart from historical data.

3

OK thanks Colin

4

Though I would say that if the ability to draw historical charts is a significant factor then I strongly suggest you look at influxdb and grafana which can easily be integrated with node-red and give very flexible historical (and real-time) graphs.

5

OK that's good to know , i will look tomorrow

6

Hi,

Unfortunately I cannot use grafana as I am using a 32bit PC.
So I have come back to trying to get database data into ui_chart node.

I have spent all off yesterday to try and understand how to do this, but have not been successful in finding an answer although I have tried many ways !

Firstly I cannot find out what format the ui_chart node requires the data to be formatted as ?

I am using an old version of NodeRED (at the moment everything has been working for years, so do not want to change at this moment, I will in the future)

12

Keeping things simple I did get this to update a chart

15
ScreenHunter_18775 Nov. 25 08.15.jpg827×633 57.2 KB

.
.
.
.
.

My NodeRED is connected like this....

41
ScreenHunter_18781 Nov. 25 08.41.jpg1008×293 21.8 KB

But gave this result

43
ScreenHunter_18782 Nov. 25 08.43.jpg829×697 114 KB

.

.

.
Any help would be appreciated as I learn best from mistakes, instead of just copying and pasting.

Thanks

7
  1. since you ae on an old version of NR your results will be different than what I see using v0.19.5 - you realu should upgrade.

In the current release, the result of an SQL query is an array of all the rows that match the result. For example:

object
_msgid: "5e5286b2.7cc468"
topic: "select * from dhtreadings"
payload: array[3]
  0: object
    id: 1
    temperature: 22.4
    humidity: 48
    currentdate: "2018-11-25"
    currenttime: "10:00:46"
    device: "manual"
  1: object
  2: object
    id: 3
    temperature: 22.4
    humidity: 48
    currentdate: "2018-11-25"
    currenttime: "10:00:49"
    device: "manual"

So the data is there, you will just have to figure out how to access the data in msg.payload in the old release you are using. you might try pushing it thru a json node...but I would recommend backing up your system and migrating to the currrent release.