OK, so the y axis is the values in the array (not the x axis as you said) and you want the time along the x axis. So in the array that you get from mssql what is the time for each sample of the ten samples that you have shown?
1 Like
The time of each sample is 30 min
That is not the answer that is needed. In your earlier post you showed that the payload contains an array of ten elements. The first one showed a value of 10.5. the question is what date/time does that sample relate to? Then similarly what is the timestamp for the second element, and all the others. Without timestamps for the samples they cannot be put on a chart.
Your question is about how to show data extracted from a database in a chart. In order to do that each record in the database must have a timestamp.
So I have to ask the question again. In your first post you showed the data from the database:
What date/time is the first sample there and what date/time is the second? So for example the first may be 3/6/21 08:35:24UTC+1. That is what it is necessary to know in order to draw the chart.
@ultim0 what fields are in your table?
Is there a datetime field? If so, you should include that field in your query.
What does your SQL query look like?
la mia query è :
SELECT TOP (10) [Temp Uscita PS3], [LocalCol] FROM [Cosma].[dbo].[PS3]
il mio datatime, dovrebbe essere LocalCol
translation
my query is:
SELECT TOP (10) [PS3 Output Temp], [LocalCol] FROM [Cosma]. [Dbo]. [PS3]
my datatime, it should be LocalCol
But your picture only has Temp Uscita PS3 value.
Nella precedente foto, non avevo selezionato il LocalCol.
Credevo bastasse visualizzare solo il valore da rappresentare sul grafico.
Come devo impostare la mia query per poterla visualizzare sul grafico?
grazie mille
In the previous photo, I hadn't selected the LocalCol.
I thought it was enough to display only the value to be represented on the graph.
How should I set up my query to be able to view it on the chart?
thank you very much
can you share a sample of data.
Click the copy value button that appears under your mouse when you hover over the payload?
Paste the copied data into a reply
```
between 3 backticks above and below the data
```
Here is a dynamic solution that works for most SQL data arrays...
I have adapted it to show you how it can work for you...
[{"id":"a80817a3d567f813","type":"inject","z":"553814a2.1248ec","name":"Fake DB data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"LocalCol\":\"2020-11-18 18:25:48.906\",\"PS3 Output Temp\":21.3},{\"LocalCol\":\"2020-11-18 18:16:23.957\",\"PS3 Output Temp\":21.6},{\"LocalCol\":\"2020-11-18 18:14:06.429\",\"PS3 Output Temp\":21.7},{\"LocalCol\":\"2020-11-18 18:13:50.943\",\"PS3 Output Temp\":21.6}]","payloadType":"json","x":130,"y":1760,"wires":[["a60265a037b7791f"]]},{"id":"a60265a037b7791f","type":"function","z":"553814a2.1248ec","name":"DB Data to Chart data","func":"/* DB Data\n[\n {\n \"Timestamp\": \"2020-11-18 18:25:48.906\",\n \"Temp\": 21.3,\n \"Light\": 15,\n \"Pressure\": 18100.2,\n \"Humidity\": 31.2\n },\n]\n*/\n/* Desired format\n[{\n\"series\": [\"temp\", \"humidity\"],\n\"data\": [\n [{ \"x\": 1504029632890, \"y\": 5 }, //series 1 data\n { \"x\": 1504029636001, \"y\": 4 },\n ],\n [{ \"x\": 1504029633514, \"y\": 6 }, //series 2 data\n { \"x\": 1504029636622, \"y\": 7 },\n ],\n],\n\"labels\": [\"\"]\n}]\n*/\n\nvar series = [];\nvar data = msg.payload;\nif (!data || !Array.isArray(data) || !data.length) {\n node.warn(\"expected an array of data with at least 1 element\");\n return null;\n //alternatively return the msg with a null payload to clear graph\n}\n\nconst tsField = \"LocalCol\";\nvar _f = Object.keys(data[0]);\nvar fields = [];\nvar fi = 0;\n_f.forEach(function(elem) {\n if (elem != tsField) {\n fields.push(elem);\n series[fi++] = [];\n }\n});\n\n//loop each row and build an array in the required format\nfor (let index = 0; index < msg.payload.length; index++) {\n const row = msg.payload[index];\n var t = new Date(row[tsField]).valueOf()\n var i = 0;\n for (let f = 0; f < fields.length; f++ ) {\n let field = fields[f];\n series[f].push({ \"x\": t, \"y\": row[field] })\n }\n}\n\n\nmsg.payload = [\n {\n \"series\": fields,\n \"data\": series,\n \"labels\": [\"\"]\n }\n];\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":360,"y":1760,"wires":[["9ea012281f268c03","e9a71972ade69462"]]},{"id":"9ea012281f268c03","type":"debug","z":"553814a2.1248ec","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":610,"y":1760,"wires":[]},{"id":"e9a71972ade69462","type":"ui_chart","z":"553814a2.1248ec","name":"","group":"dea21ffd.1483","order":4,"width":"6","height":"7","label":"data","chartType":"line","legend":"true","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"2","removeOlderPoints":"1000","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"x":590,"y":1820,"wires":[[]]},{"id":"dea21ffd.1483","type":"ui_group","name":"Pump Status","tab":"dd590918.d334c8","order":1,"disp":true,"width":"6","collapse":false},{"id":"dd590918.d334c8","type":"ui_tab","name":"Pool Control","icon":"dashboard","disabled":false,"hidden":false}]
Ma in questo caso tu gli stai passando dei valori statici, io vorrei comporre un grafico dove i valori vengono passati dalla query.
Come posso fare?
But in this case you are passing it some static values, I would like to compose a graph where the values are passed by the query.
How can I do?
I dont have your database so had to use static values.
Delete the inject and connect it to your database node.
grazie mille, mi hai salvato la vita
You are welcome.
If it works for you, can you mark the post as solved?
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.
