# Dynamic Data Conversion
**URL:** https://discourse.nodered.org/t/dynamic-data-conversion/92728
**Category:** General
**Tags:** function-node
**Created:** [28 October 2024 08:05 UTC](https://discourse.nodered.org/t/dynamic-data-conversion/92728 "2024-10-28T08:05:41Z")
**Posts on this page:** 16
**Page:** 1
### Author:  [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)
#### Post date: [28 October 2024 08:05 UTC](https://discourse.nodered.org/t/dynamic-data-conversion/92728/1 "2024-10-28T08:05:41Z")
I am unable to state my issue, so giving example
what I have
```auto
[{"id":"d42c55ce149d9aa6","type":"inject","z":"0293de8750223951","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"FROM\":\"2024,10,27,06,00\",\"To\":\"2024,10,27,06,20\",\"DUR\":20,\"mcn\":\"m03\",\"state\":\"OFF\"},{\"FROM\":\"2024,10,27,06,20\",\"To\":\"2024,10,27,06,21\",\"DUR\":1,\"mcn\":\"m03\",\"state\":\"ON\"},{\"FROM\":\"2024,10,27,06,21\",\"To\":\"2024,10,27,06,35\",\"DUR\":14,\"mcn\":\"m03\",\"state\":\"OFF\"},{\"FROM\":\"2024,10,27,06,35\",\"To\":\"2024,10,27,06,45\",\"DUR\":10,\"mcn\":\"m03\",\"state\":\"ON\"},{\"FROM\":\"2024,10,27,06,45\",\"To\":\"2024,10,27,06,51\",\"DUR\":6,\"mcn\":\"m03\",\"state\":\"OFF\"},{\"FROM\":\"2024,10,27,06,51\",\"To\":\"2024,10,27,07,04\",\"DUR\":53,\"mcn\":\"m03\",\"state\":\"ON\"},{\"FROM\":\"2024,10,27,07,04\",\"To\":\"2024,10,27,07,15\",\"DUR\":11,\"mcn\":\"m03\",\"state\":\"OFF\"},{\"FROM\":\"2024,10,27,07,15\",\"To\":\"2024,10,27,07,34\",\"DUR\":19,\"mcn\":\"m03\",\"state\":\"ON\"},{\"FROM\":\"2024,10,27,07,34\",\"To\":\"2024,10,27,08,03\",\"DUR\":69,\"mcn\":\"m03\",\"state\":\"OFF\"},{\"FROM\":\"2024,10,27,09,37\",\"To\":\"2024,10,27,09,42\",\"DUR\":5,\"mcn\":\"m03\",\"state\":\"ON\"},{\"FROM\":\"2024,10,27,09,42\",\"To\":\"2024,10,27,10,25\",\"DUR\":83,\"mcn\":\"m03\",\"state\":\"OFF\"},{\"FROM\":\"2024,10,27,10,25\",\"To\":\"2024,10,27,10,46\",\"DUR\":21,\"mcn\":\"m03\",\"state\":\"ON\"},{\"FROM\":\"2024,10,27,10,46\",\"To\":\"2024,10,27,11,04\",\"DUR\":58,\"mcn\":\"m03\",\"state\":\"OFF\"},{\"FROM\":\"2024,10,27,11,04\",\"To\":\"2024,10,27,11,25\",\"DUR\":21,\"mcn\":\"m03\",\"state\":\"ON\"},{\"FROM\":\"2024,10,27,11,25\",\"To\":\"2024,10,27,11,53\",\"DUR\":28,\"mcn\":\"m03\",\"state\":\"OFF\"},{\"FROM\":\"2024,10,27,11,53\",\"To\":\"2024,10,27,12,07\",\"DUR\":54,\"mcn\":\"m03\",\"state\":\"ON\"},{\"FROM\":\"2024,10,27,12,07\",\"To\":\"2024,10,27,12,12\",\"DUR\":5,\"mcn\":\"m03\",\"state\":\"OFF\"},{\"FROM\":\"2024,10,27,12,12\",\"To\":\"2024,10,27,12,20\",\"DUR\":8,\"mcn\":\"m03\",\"state\":\"ON\"},{\"FROM\":\"2024,10,27,12,20\",\"To\":\"2024,10,27,12,22\",\"DUR\":2,\"mcn\":\"m03\",\"state\":\"OFF\"},{\"FROM\":\"2024,10,27,12,22\",\"To\":\"2024,10,27,12,58\",\"DUR\":36,\"mcn\":\"m03\",\"state\":\"ON\"}]","payloadType":"json","x":440,"y":1320,"wires":[["b98e1677c2a9927f","b52ef14ec1f098c4"]]}]
```
What I need
I am able to do it manually 1 by 1 like below is the output
but to create dynamic function to loop through the entire data, i am not able to as the input data quantity varies (in the example it is 20 records)
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [28 October 2024 08:49 UTC](https://discourse.nodered.org/t/dynamic-data-conversion/92728/3 "2024-10-28T08:49:27Z")
> [@smanjunath211](#):
>
> I am able to do it manually 1 by 1 like below is the output
>
> 
I assume you do not actually want `JavaScript` in the array but actual valid ISO date data strings.
Here is a function that generates an array of arrays of ["ON", "iso-time-string", "iso-time-string"]
```auto
msg.payload = msg.payload.map(e=> {
const [fromY, fromMo, fromD, fromH, fromMi] = e.FROM.split(",")
const [toY, toMo, toD, toH, toMi] = e.To.split(",")
return [
e.state,
new Date(fromY, fromMo, fromD, fromH, fromMi, 0, 0),
new Date(toY, toMo, toD, toH, toMi, 0, 0)
]
})
return msg;
```
Proof
Sample Data (3 rows)
```auto
[
[
"OFF",
"2024-11-27T06:00:00.000Z",
"2024-11-27T06:20:00.000Z"
],
[
"ON",
"2024-11-27T06:20:00.000Z",
"2024-11-27T06:21:00.000Z"
],
[
"OFF",
"2024-11-27T06:21:00.000Z",
"2024-11-27T06:35:00.000Z"
]
]
```
* * *
PS, if you have any control over the incoming date format, I would change that to send ISO time strings instead of custom CSV!
---
### Author:  [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)
#### Post date: [28 October 2024 09:01 UTC](https://discourse.nodered.org/t/dynamic-data-conversion/92728/4 "2024-10-28T09:01:23Z")
I am formating mysql query to give me readable datetime as i am also simultaneously displaying a table, but i can get the time in rawformat as well.
the part i want to replace is in a bigger template node, where the number of rows are dynamic.
currently i am able to get it with flow.context (somehow msg.payload is not working with mustache template, but flow.context works) thats another subject of discussion though.
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [28 October 2024 09:05 UTC](https://discourse.nodered.org/t/dynamic-data-conversion/92728/5 "2024-10-28T09:05:52Z")
Bit strange, but ok.
You can still use the function I provided (plus the extra parts you want) then stringify it (pass it through a JSON node) and add the whole array to your template via mustache
`dataTable.addRows( {{{ theData }}} )`
where `theData` is the stringified array dynamically generated and passed in or stored in context
---
### Author:  [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)
#### Post date: [28 October 2024 09:17 UTC](https://discourse.nodered.org/t/dynamic-data-conversion/92728/6 "2024-10-28T09:17:53Z")
thank you, I was able to get the `theData` into the template node, but the dateobject is not rendering in the chart, i think it needs to be in this format only.
`new Date(year,month,date,hour,minute)` how do i change that ?
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [28 October 2024 10:36 UTC](https://discourse.nodered.org/t/dynamic-data-conversion/92728/7 "2024-10-28T10:36:59Z")
> [@Steve-Mcl](#):
>
> Bit strange
There is a separate thread where I've described how to avoid this output. 😄
---
### Author:  [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)
#### Post date: [28 October 2024 11:25 UTC](https://discourse.nodered.org/t/dynamic-data-conversion/92728/8 "2024-10-28T11:25:21Z")
Only this works to render chart successfully. Note the single quotes for first two elements `m04` and `off` /`on`
```auto
dataTable.addRows([
['m04','off',new Date(2024,10,27,06,00),new Date(2024,10,27,06,03)],
['m04','on',new Date(2024,10,27,06,03),new Date(2024,10,27,06,07)],
['m04','off',new Date(2024,10,27,06,07),new Date(2024,10,27,06,09)],
]);
```
I am getting either this single quotes for all elements, which does not work
```auto
dataTable.addRows([
['m04','off','new Date(2024,10,27,06,00)','new Date(2024,10,27,06,03)'],
['m04','on','new Date(2024,10,27,06,03)','new Date(2024,10,27,06,07)'],
['m04','off','new Date(2024,10,27,06,07)','new Date(2024,10,27,06,09)]',
]);
```
or this no quotes at all which doesn't work.
```auto
dataTable.addRows([
[m04,off,new Date(2024,10,27,06,00),new Date(2024,10,27,06,03)],
[m04,on,new Date(2024,10,27,06,03),new Date(2024,10,27,06,07)],
[m04,off,new Date(2024,10,27,06,07),new Date(2024,10,27,06,09)],
]);
```
this is the function code i am using
```auto
var data = msg.payload;
msg.payload = msg.payload.map(e => {
return [
e.mcn,
e.state,
e.FROM,
e.To
]
})
return msg;
```
I am getting mysql query to output the data in the format new Date(Y,m,D,H,i)
MySql Query
> **Summary**
>
> ```auto
> TRUNCATE tmp;
> Insert into tmp Select sort, if({{flow.selmc}}>0,'ON','OFF') AS state, datetime, '{{flow.selmc}}','' FROM rawdata where rdate='{{flow.seldate}}' and shift like '{{flow.selshift}}' order by sort;
> Insert into tmp select (id-1), if (state='OFF','ON','OFF'), date_sub(datetime,interval 1 minute) ,mcn,'' from tmp limit 1;
> SELECT concat("new Date(",date_format(date_sub((LAG(t2.datetime) over (order by t2.datetime)), interval 1 minute),'%Y,%m,%d,%H,%i'),")") as 'FROM' ,
> concat("new Date(",date_format(t1.datetime,'%Y,%m,%d,%H,%i'),")") as 'To', round(((t1.datetime) - (LAG(t2.datetime) over (order by t2.datetime))) /100,0)+1 AS `DUR`,
> t1.mcn, t1.state FROM tmp t1 JOIN tmp t2 ON t1.id= t2.id- 1 WHERE t1.state != t2.state limit 1,480;
>
> ```
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [28 October 2024 13:18 UTC](https://discourse.nodered.org/t/dynamic-data-conversion/92728/9 "2024-10-28T13:18:27Z")
You are getting yourself confused between different data types. You need output from your SQL query to have date/times as something that JavaScript can recognise.
Then, as you are using this library: [Jadwal | Charts | Google for Developers](https://developers.google.com/chart/interactive/docs/gallery/timeline#an-advanced-example)
You've seen that their example for adding rows looks like this:
```javascript
dataTable.addRows([
['President', 'George Washington', new Date(1789, 3, 30), new Date(1797, 2, 4)],
['Secretary of State', 'John Jay', new Date(1789, 8, 25), new Date(1790, 2, 22)],
['Secretary of State', 'James Madison', new Date(1801, 4, 2), new Date(1809, 2, 3)]
]);
```
So it clearly wants the date as a JavaScript Date object which is what `new Date` creates. You cannot output the `new Date` part from your SQL query since you have told it to output STRINGS for the dates. SQL cannot directly output a JavaScript function which is what you need.
Since you are using UIBUILDER, there is a really simple answer here. Just output the data from SQL using a recognisable date/time format - typically in ISO format that looks like `2024-10-28T13:06:00.000Z` or, if you can't work out how to do that, output as `2024-10-28 13:06:00` which will be close enought for this.
Then you convert the date string to a date object. This is best done in the front-end code since you are already having to use that.
```javascript
uibuilder.onChange('msg', (msg) => {
msg.payload.forEach( (row) => {
dataTable.addRows( [
[row.mcn, row.state, new Date(row.From), new Date(row.To)]
] );
} );
```
Note: I may not have got your data properties named correctly, check the data coming out of your SQL query to get the exact format.
Basically, rule number 1 of programming - don't do what the computer can do for you.
This code runs in the browser. It listens for a message from Node-RED (via the uibuilder client helper library). It assumes that your SQL output is in msg.payload. It walks through each SQL output row (the `forEach`) and adds a new dataTable row for each SQL row. In that process, it creates JavaScript date objects from the text version of the date.
---
### Author:  [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)
#### Post date: [28 October 2024 15:56 UTC](https://discourse.nodered.org/t/dynamic-data-conversion/92728/10 "2024-10-28T15:56:41Z")
You will have to bear with me....
this is the output i get from MySQL Query, now how do i write the `uibuilder.onChange` function ?
```auto
[{"mcn":"m02","state":"OFF","FROM":"2024-10-27T00:30:00.000Z","To":"2024-10-27T05:26:00.000Z"},{"mcn":"m02","state":"ON","FROM":"2024-10-27T05:26:00.000Z","To":"2024-10-27T05:27:00.000Z"},{"mcn":"m02","state":"OFF","FROM":"2024-10-27T05:27:00.000Z","To":"2024-10-27T05:42:00.000Z"},{"mcn":"m02","state":"ON","FROM":"2024-10-27T05:42:00.000Z","To":"2024-10-27T05:43:00.000Z"},{"mcn":"m02","state":"OFF","FROM":"2024-10-27T05:43:00.000Z","To":"2024-10-27T05:53:00.000Z"},{"mcn":"m02","state":"ON","FROM":"2024-10-27T05:53:00.000Z","To":"2024-10-27T05:54:00.000Z"}]
```
Sample data in an inject node...
```auto
[{"id":"6ab576041f967758","type":"inject","z":"0293de8750223951","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"mcn\":\"m02\",\"state\":\"OFF\",\"FROM\":\"2024-10-27T00:30:00.000Z\",\"To\":\"2024-10-27T05:26:00.000Z\"},{\"mcn\":\"m02\",\"state\":\"ON\",\"FROM\":\"2024-10-27T05:26:00.000Z\",\"To\":\"2024-10-27T05:27:00.000Z\"},{\"mcn\":\"m02\",\"state\":\"OFF\",\"FROM\":\"2024-10-27T05:27:00.000Z\",\"To\":\"2024-10-27T05:42:00.000Z\"},{\"mcn\":\"m02\",\"state\":\"ON\",\"FROM\":\"2024-10-27T05:42:00.000Z\",\"To\":\"2024-10-27T05:43:00.000Z\"},{\"mcn\":\"m02\",\"state\":\"OFF\",\"FROM\":\"2024-10-27T05:43:00.000Z\",\"To\":\"2024-10-27T05:53:00.000Z\"},{\"mcn\":\"m02\",\"state\":\"ON\",\"FROM\":\"2024-10-27T05:53:00.000Z\",\"To\":\"2024-10-27T05:54:00.000Z\"}]","payloadType":"json","x":220,"y":1740,"wires":[["9d6eb06bb46d9fa7"]]},{"id":"9d6eb06bb46d9fa7","type":"debug","z":"0293de8750223951","name":"debug 10","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":350,"y":1740,"wires":[]}]
```
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [28 October 2024 16:08 UTC](https://discourse.nodered.org/t/dynamic-data-conversion/92728/11 "2024-10-28T16:08:56Z")
OK, here's somethng to get you going.
This flow takes your input and outputs something that is easily used. Then you should be able to use my previous front-end code but with 1 change, `row.From` becomes `row.FROM`.
---
### Author:  [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)
#### Post date: [28 October 2024 16:11 UTC](https://discourse.nodered.org/t/dynamic-data-conversion/92728/12 "2024-10-28T16:11:16Z")
what is in the change node ?
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [28 October 2024 16:49 UTC](https://discourse.nodered.org/t/dynamic-data-conversion/92728/13 "2024-10-28T16:49:44Z")
Oops! Sorry, I didn't post the actual flow! 😄
```auto
[{"id":"a6f065865938f608","type":"inject","z":"1ceef65e4021de52","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"id\":\"6ab576041f967758\",\"type\":\"inject\",\"z\":\"0293de8750223951\",\"name\":\"\",\"props\":[{\"p\":\"payload\"}],\"repeat\":\"\",\"crontab\":\"\",\"once\":false,\"onceDelay\":0.1,\"topic\":\"\",\"payload\":\"[{\\\"mcn\\\":\\\"m02\\\",\\\"state\\\":\\\"OFF\\\",\\\"FROM\\\":\\\"2024-10-27T00:30:00.000Z\\\",\\\"To\\\":\\\"2024-10-27T05:26:00.000Z\\\"},{\\\"mcn\\\":\\\"m02\\\",\\\"state\\\":\\\"ON\\\",\\\"FROM\\\":\\\"2024-10-27T05:26:00.000Z\\\",\\\"To\\\":\\\"2024-10-27T05:27:00.000Z\\\"},{\\\"mcn\\\":\\\"m02\\\",\\\"state\\\":\\\"OFF\\\",\\\"FROM\\\":\\\"2024-10-27T05:27:00.000Z\\\",\\\"To\\\":\\\"2024-10-27T05:42:00.000Z\\\"},{\\\"mcn\\\":\\\"m02\\\",\\\"state\\\":\\\"ON\\\",\\\"FROM\\\":\\\"2024-10-27T05:42:00.000Z\\\",\\\"To\\\":\\\"2024-10-27T05:43:00.000Z\\\"},{\\\"mcn\\\":\\\"m02\\\",\\\"state\\\":\\\"OFF\\\",\\\"FROM\\\":\\\"2024-10-27T05:43:00.000Z\\\",\\\"To\\\":\\\"2024-10-27T05:53:00.000Z\\\"},{\\\"mcn\\\":\\\"m02\\\",\\\"state\\\":\\\"ON\\\",\\\"FROM\\\":\\\"2024-10-27T05:53:00.000Z\\\",\\\"To\\\":\\\"2024-10-27T05:54:00.000Z\\\"}]\",\"payloadType\":\"json\",\"x\":220,\"y\":1740,\"wires\":[[\"9d6eb06bb46d9fa7\"]]},{\"id\":\"9d6eb06bb46d9fa7\",\"type\":\"debug\",\"z\":\"0293de8750223951\",\"name\":\"debug 10\",\"active\":true,\"tosidebar\":true,\"console\":false,\"tostatus\":false,\"complete\":\"false\",\"statusVal\":\"\",\"statusType\":\"auto\",\"x\":350,\"y\":1740,\"wires\":[]}]","payloadType":"json","x":290,"y":960,"wires":[["0e0b44fbf90757d0"]]},{"id":"f7449e09266a99ad","type":"json","z":"1ceef65e4021de52","name":"","property":"payload","action":"","pretty":false,"x":630,"y":960,"wires":[["0e16b227d046fb12"]]},{"id":"0e0b44fbf90757d0","type":"change","z":"1ceef65e4021de52","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[0].payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":460,"y":960,"wires":[["f7449e09266a99ad"]]},{"id":"0e16b227d046fb12","type":"debug","z":"1ceef65e4021de52","name":"debug 2724","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":810,"y":960,"wires":[]}]
```
---
### Author:  [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)
#### Post date: [29 October 2024 06:27 UTC](https://discourse.nodered.org/t/dynamic-data-conversion/92728/14 "2024-10-29T06:27:08Z")