# From Mysql to chart, but many colums in one raw

**URL:** <https://discourse.nodered.org/t/from-mysql-to-chart-but-many-colums-in-one-raw/69534>\
**Category:** General\
**Tags:** database\
**Created:** [25 October 2022 07:30 UTC](https://discourse.nodered.org/t/from-mysql-to-chart-but-many-colums-in-one-raw/69534 "2022-10-25T07:30:19Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![axdig](https://avatars.discourse-cdn.com/v4/letter/a/58956e/32.png) [@axdig](https://discourse.nodered.org/u/axdig)\
**Post date:** [25 October 2022 07:30 UTC](https://discourse.nodered.org/t/from-mysql-to-chart-but-many-colums-in-one-raw/69534/1 "2022-10-25T07:30:19Z")

</div>

Hi.  
I'm stuck, I have a table where I save values ​​every hour, every hour has its own column, so there are 24 columners on each date. now I want to make a line diagram from this, but I don't succeed. can someone give me a hint how to do ☺

This is my array:

[{"10":569.25,"11":522.3,"12":386.1,"13":366.75,"14":353.1,"15":351.45,"16":342.9,"17":350.55,"18":353.7,"19":344.7,"20":364.2,"21":406.05,"22":425.25,"23":405.45,"ID":98,"DeviceID":1,"Datum":"2022-10-20","00":283.05,"01":272.7,"02":253.35,"03":227.25,"04":238.95,"05":424.35,"06":542.85,"07":549.75,"08":529.8,"09":561.15}]

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [25 October 2022 07:47 UTC](https://discourse.nodered.org/t/from-mysql-to-chart-but-many-colums-in-one-raw/69534/2 "2022-10-25T07:47:25Z")

</div>

I would suggest to store the values with a normal timestamp instead.

example

```auto
insert into data (created_on, device_id,value) values ("YYYY-MM-DD HH:MI:SS", deviceid, thevalue)

```

Once you have the data in this format you can use normal sql aggregations to group the data by hour, minute, day etc.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [25 October 2022 09:20 UTC](https://discourse.nodered.org/t/from-mysql-to-chart-but-many-colums-in-one-raw/69534/3 "2022-10-25T09:20:48Z")

</div>

As bakman2 suggest it would be best to store the data or retrieve the data in the correct format.

If that is not possible and you have no control then you need to convert and sort the array object and property names to valid timestamp formats the chart understands.  
this should work

```auto
[{"id":"e7703a8016f83494","type":"inject","z":"452103ea51141731","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"10\":569.25,\"11\":522.3,\"12\":386.1,\"13\":366.75,\"14\":353.1,\"15\":351.45,\"16\":342.9,\"17\":350.55,\"18\":353.7,\"19\":344.7,\"20\":364.2,\"21\":406.05,\"22\":425.25,\"23\":405.45,\"ID\":98,\"DeviceID\":1,\"Datum\":\"2022-10-20\",\"00\":283.05,\"01\":272.7,\"02\":253.35,\"03\":227.25,\"04\":238.95,\"05\":424.35,\"06\":542.85,\"07\":549.75,\"08\":529.8,\"09\":561.15}]","payloadType":"json","x":150,"y":1480,"wires":[["636fe812456bfac6"]]},{"id":"636fe812456bfac6","type":"function","z":"452103ea51141731","name":"function 13","func":"let output = [];\nconst date = msg.payload[0].Datum\nfor(const [key, value] of Object.entries(msg.payload[0])){\n if(!isNaN(parseInt(key))){\n let date_convert = new Date(date + \"T\" + key.padStart(2, \"0\") + \":00:00.000Z\").valueOf()\n output.push( {x: date_convert, y: value} )\n }\n}\noutput.sort((a,b) => a.x - b.x)\nmsg.date = date\nmsg.payload = [\n {\n series: [msg.payload[0].deviceID],\n data:[output],\n labels:[msg.payload[0].deviceID]\n }\n]\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":430,"y":1540,"wires":[["a9c19672763f5f06"]]},{"id":"a9c19672763f5f06","type":"ui_chart","z":"452103ea51141731","name":"","group":"2d4fe667.28f8ba","order":16,"width":0,"height":0,"label":"Readings {{msg.date}}","chartType":"line","legend":"false","xformat":"HH","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":550,"y":1500,"wires":[["5c9cb54139586ae3"]]},{"id":"5c9cb54139586ae3","type":"debug","z":"452103ea51141731","name":"debug 96","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":640,"y":1440,"wires":[]},{"id":"2d4fe667.28f8ba","type":"ui_group","name":"demo","tab":"1caa8458.b17814","order":2,"disp":true,"width":"12","collapse":false},{"id":"1caa8458.b17814","type":"ui_tab","name":"Demo","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

```

function

```auto
let output = [];
const date = msg.payload[0].Datum
for(const [key, value] of Object.entries(msg.payload[0])){
    if(!isNaN(parseInt(key))){
        let date_convert = new Date(date + "T" + key.padStart(2, "0") + ":00:00.000Z").valueOf()
        output.push( {x: date_convert, y: value} )
    }
}
output.sort((a,b) => a.x - b.x)
msg.date = date
msg.payload = [ 
    {
        series: [msg.payload[0].deviceID],
        data:[output],
        labels:[msg.payload[0].deviceID]
    }
]
return msg;

```

---

<div class="post-metadata">

**Author:** ![axdig](https://avatars.discourse-cdn.com/v4/letter/a/58956e/32.png) [@axdig](https://discourse.nodered.org/u/axdig)\
**Post date:** [25 October 2022 11:24 UTC](https://discourse.nodered.org/t/from-mysql-to-chart-but-many-colums-in-one-raw/69534/4 "2022-10-25T11:24:36Z")

</div>

Many thanks, I'll see if I can manage the data in a simpler form. otherwise your code E1cid works perfectly!

---

<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:** [8 November 2022 11:25 UTC](https://discourse.nodered.org/t/from-mysql-to-chart-but-many-colums-in-one-raw/69534/5 "2022-11-08T11:25:24Z")

</div>

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