# I need help, chart-ui from select sql server

**URL:** <https://discourse.nodered.org/t/i-need-help-chart-ui-from-select-sql-server/46633>\
**Category:** Dashboard\
**Created:** [3 June 2021 07:13 UTC](https://discourse.nodered.org/t/i-need-help-chart-ui-from-select-sql-server/46633 "2021-06-03T07:13:06Z")\
**Posts on this page:** 16\
**Page:** 2

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [3 June 2021 16:05 UTC](https://discourse.nodered.org/t/i-need-help-chart-ui-from-select-sql-server/46633/21 "2021-06-03T16:05:51Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![ultim0](https://avatars.discourse-cdn.com/v4/letter/u/ecccb3/32.png) [@ultim0](https://discourse.nodered.org/u/ultim0)\
**Post date:** [7 June 2021 06:42 UTC](https://discourse.nodered.org/t/i-need-help-chart-ui-from-select-sql-server/46633/22 "2021-06-07T06:42:43Z")

</div>

The time of each sample is 30 min

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [7 June 2021 07:33 UTC](https://discourse.nodered.org/t/i-need-help-chart-ui-from-select-sql-server/46633/23 "2021-06-07T07:33:53Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ultim0](https://avatars.discourse-cdn.com/v4/letter/u/ecccb3/32.png) [@ultim0](https://discourse.nodered.org/u/ultim0)\
**Post date:** [7 June 2021 07:46 UTC](https://discourse.nodered.org/t/i-need-help-chart-ui-from-select-sql-server/46633/24 "2021-06-07T07:46:24Z")

</div>

Il valore del timestamp, fatto in maniera ripetitiva, è di 5 secondi.

 ![01](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/f/dfa69549de7d9dea3e80625d2394002071008faa.jpeg)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [7 June 2021 09:02 UTC](https://discourse.nodered.org/t/i-need-help-chart-ui-from-select-sql-server/46633/25 "2021-06-07T09:02:14Z")

</div>

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:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/c/4c05b2b92ecf3c83058206324f5c280192d302a8.jpeg)

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.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 June 2021 09:06 UTC](https://discourse.nodered.org/t/i-need-help-chart-ui-from-select-sql-server/46633/26 "2021-06-07T09:06:14Z")

</div>

@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?

---

<div class="post-metadata">

**Author:** ![ultim0](https://avatars.discourse-cdn.com/v4/letter/u/ecccb3/32.png) [@ultim0](https://discourse.nodered.org/u/ultim0)\
**Post date:** [7 June 2021 09:12 UTC](https://discourse.nodered.org/t/i-need-help-chart-ui-from-select-sql-server/46633/27 "2021-06-07T09:12:18Z")

</div>

la mia query è :  
SELECT TOP (10) [Temp Uscita PS3], [LocalCol] FROM [Cosma].[dbo].[PS3]  
il mio datatime, dovrebbe essere LocalCol

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 June 2021 09:14 UTC](https://discourse.nodered.org/t/i-need-help-chart-ui-from-select-sql-server/46633/28 "2021-06-07T09:14:47Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ultim0](https://avatars.discourse-cdn.com/v4/letter/u/ecccb3/32.png) [@ultim0](https://discourse.nodered.org/u/ultim0)\
**Post date:** [7 June 2021 09:18 UTC](https://discourse.nodered.org/t/i-need-help-chart-ui-from-select-sql-server/46633/29 "2021-06-07T09:18:08Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 June 2021 09:22 UTC](https://discourse.nodered.org/t/i-need-help-chart-ui-from-select-sql-server/46633/30 "2021-06-07T09:22:13Z")

</div>

can you share a sample of data.

Click the `copy value` button that appears under your mouse when you hover over the payload?

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/6/46c1c21ef8d67706b0d2b015b277eaae86d3f47d.png)

Paste the copied data into a reply  
`````  
`between 3 backticks above and below the data`  
`````

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 June 2021 09:39 UTC](https://discourse.nodered.org/t/i-need-help-chart-ui-from-select-sql-server/46633/31 "2021-06-07T09:39:33Z")

</div>

Here is a dynamic solution that works for most SQL data arrays...

> [@Dynamic series identification from message, send data to Chart](https://discourse.nodered.org/t/dynamic-series-identification-from-message-send-data-to-chart/36682/5):
>
> So, i am not 100% certain what you are after but if (by the title "dynamic series") you mean sending alternative types of data from a database (i.e. varying fields and varying quantities) then this should get you close. It was a simple adaption of the previous solution I pointed you to, modified to build the series from the field names of the 1st data element in the data passed in. There are 2 injects to simulate two different sets of mySQL data [image] [{"id":"227e8b13.87c804…

I have adapted it to show you how it can work for you...

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/c/cce3ea2a193ae8d0ca4b7b59aeeb4016a13ae276.png)

```auto
[{"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}]

```

---

<div class="post-metadata">

**Author:** ![ultim0](https://avatars.discourse-cdn.com/v4/letter/u/ecccb3/32.png) [@ultim0](https://discourse.nodered.org/u/ultim0)\
**Post date:** [7 June 2021 09:58 UTC](https://discourse.nodered.org/t/i-need-help-chart-ui-from-select-sql-server/46633/32 "2021-06-07T09:58:47Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 June 2021 10:00 UTC](https://discourse.nodered.org/t/i-need-help-chart-ui-from-select-sql-server/46633/33 "2021-06-07T10:00:00Z")

</div>

I dont have your database so had to use static values.

Delete the inject and connect it to your database node.

---

<div class="post-metadata">

**Author:** ![ultim0](https://avatars.discourse-cdn.com/v4/letter/u/ecccb3/32.png) [@ultim0](https://discourse.nodered.org/u/ultim0)\
**Post date:** [7 June 2021 10:02 UTC](https://discourse.nodered.org/t/i-need-help-chart-ui-from-select-sql-server/46633/34 "2021-06-07T10:02:51Z")

</div>

grazie mille, mi hai salvato la vita

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 June 2021 10:07 UTC](https://discourse.nodered.org/t/i-need-help-chart-ui-from-select-sql-server/46633/35 "2021-06-07T10:07:28Z")

</div>

You are welcome.

If it works for you, can you mark the post as solved?

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/5/25bbb4ab7824528fca8877bcd81467c95926f867.png)

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [21 June 2021 10:07 UTC](https://discourse.nodered.org/t/i-need-help-chart-ui-from-select-sql-server/46633/36 "2021-06-21T10:07:39Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.

[Previous page](https://discourse.nodered.org/t/i-need-help-chart-ui-from-select-sql-server/46633.md?page=1)