Dear friends,
I have a SQLite table with such data :
It stores sensor data .Column "device" stores sensor names and "val" stores their values.
Now ,haw can I show such data in a table that shows all sensor and their values ?
Thanks
Have you looked at the ui_table node?
Yes Of course !
I run it trough another example.But that SQLite table structure was different.There I have different sensors in different columns and feed ui_table was simple :
But here I have one column for all sensor names,and another for their values :
My only problem is how I can get the query ...
Do you mean you want to show all the data ordered by the sensor?
If so you will need to add that to your query. Do a google search using 'sqlite order by' to find the syntax.
I have searched a lot ,but no answer !
the last result is :
SELECT id,
(
SELECT val
FROM nplc
WHERE device = "temperature3"
)
temp3,
(
SELECT val
FROM nplc
WHERE device = "temperature4"
)
temp4,
(
SELECT val
FROM nplc
WHERE device = "humidity3"
)
humi3,
(
SELECT val
FROM nplc
WHERE device = "humidity4"
)
humi4,
thetime
FROM nplc
GROUP BY
thetime
ORDER BY thetime DESC;
But the result :
Every thing seems ok ,but the problem is that only 1st row is correct ,and it is repeating in other rows ! 
What is the schema of the table?
I send the table with data look later in 3th post :
https://discourse.nodered.org/t/how-to-feed-table-with-such-a-sqlite-table/24415/3?u=tarahi
And SQLite structure is :
CREATE TABLE nplc (id INTEGER PRIMARY KEY AUTOINCREMENT,device TEXT,val TEXT, thetime DATETIME DEFAULT (datetime('now','localtime')))
You can use ui-table and feed the data using commands if all values you like to combine in one row have a common index (perhaps the timestamp)
Design a table with the columns you need: time, temp1, hum1, temp2, hum3 ....
define the timestamp is your index field.
Then feed your ui-table with objects like this (pseudo code!)
msg.payload={
"command":"updateOrAddData",
"arguments": [{"time":timestamp,"temp1":value}],
"returnPromise": false
};
ui-table can add or update new values to existing rows (identified by the index) or add new rows if the index is new / not existing.
But all depends on that you have a common field indicating which values goes into a row.
Certainly you can do this with an sql query (in which I'm not an expert) but you will again need a value indicating which values goes in one row. As I see your data it don`t look like you have that. All values look like they are written as they arrive.
If you like to combine sensors with there latest value the sensorName is your index.
Perhaps you can visualize how your table should look like in the end.
Dear Friend,
As I told earlier ,I have made a ui_table with such a SQLite table that you recommend.
Now the main problem is ,how to get a suitable query for this kind of table :
Sorry I can`t help you with SQL. I misunderstood your perhaps as I thought you would like to combine data in a different way. Like putting the devices in columns and the rows are defined by the timestamps (because this is possible by ui-table itself when you receive your data as shown in your screenshot.
| time | temp1 | hum1 | temp2 | hum2 |
|---|---|---|---|---|
| 20:35 | 28.0 | 22% | 28.3 | 27.6% |
| 20:36 | 29.0 | 25% | 28.4 | 27.6% |
Right,
Let me ask my Question in another form ,
How can I convert my sqlite table in this form :
to this :
never mind with a single query or combine four queries like this for each time :
SELECT val FROM nplc WHERE device = "temperature3";
In the database you have mulitple rows with the same timestamp, but you want them on the same row in the ui_table. I am not sure whether you can do that with SQL, but as @Christian-Me says that isn't a question for this forum.
If I had this problem then I would write some javascript in a function node to loop through the rows, extracting the data in the appropriate form for the table.
Hello,
How would You like to merge the rows?
If look at the first 4 rows, there is 2 different timestamps. If You aggregate on that, it will give 2 rows. 1 with data from id 1, and 1 with data from id 2-4.
There need to be a key in each row, to combine the rows. It might be thetime, or something else.
You can round the timestamp to a key, but then You risk the same key in 1 - more than 4 rows, with data from different sensors. Will You then have max, min, average if there is multiple values from one sensor.
If a sensor have not stored a value, what will You then show? Nothing (null), the last, or?
I think it can be done with sql. But I think You should take f.ex. 8 or 12 rows, and show manually, row for row, what You will show in the table You convert data to. Then You Can build You logic, which else might contain some randomness, or not what You want.
It might also be an idear only to convert the amount of data You want in the table. With 16 rows, no problem, but if You have years, will You still show all data, or only the last hour or day.
Best regards
Brian
I could think about this: Make the table a resolution of 10sec (cut off all the "noise")
timestamp=Math.floor(msg.timestamp/10000)*10000; //60000 for one minute
and then feed the data into ui-table as described before.
You will get all your data per 10 second interval (if any arrives) aggregated in one row, showing always the recent one if more than one arrives in this period.
And by the way ... perhaps connect your sensor directly to Node-RED and forward then to your database
Dear friends,
Colin
zenofmud
Brian5600
Christian-Me
Thanks for yor quick responses,Here is one of the best forums I've ever seen.Quick response with New elegant forum look 
First I will try to find the SQLite code ,But if impossible,2nd ,I will try to write a code to specify and join sensor data according to their times.
3 Likes
You might already using one of these but I would use one of the many query builder tools to interactively build and debug your sql query (like this one / just googled it: https://github.com/sqlitebrowser/sqlitebrowser)
1 Like
I find similar process in :
Pivot — Rows to Columns
How to pivot in SQLite or i.e. select in wide format a table stored in long format?
1 Like
Hello Tarahi
Yes, thats nice. The second link, have some excamples, which can be copied right away.
Best regards
Brian
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
