From Mysql to chart, but many colums in one raw

General
1

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 :smiling_face:

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}]

2

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

example

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.

3

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

[{"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

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;
4

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

5

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