Modify datetime in array for proper chart ui output

Dashboard
1

I feel like this has been asked many times, yet none of the existing thread I could find speak to my issue specifically. There is something I don't understand in how to handle the data coming from an SQL query that I want to put into a bar chart. The X axis is automatically populated with datetime, but I only pass Date from my SQL query (meaning time in the chart is always 00:00:00Z) and this makes the chart hard to read.
Essentially, all I'm looking to do is drop the time and keep the date and while I can sucessfully do that with a single message using the moment node, I can't seem to find a way to change the whole array fed to the chart. Can someone point me in the right direction please ?

[{"id":"5d4be9cd3f2fb5a7","type":"group","z":"1be556fa6eb0cb07","name":"Eggs graphics","style":{"label":true},"nodes":["d8b58e7bb50780ea","3947e94b6414de44","600fa4ed171d49f1","6ec0233fa9f2c446","47158d87bc0bc360"],"x":974,"y":39,"w":822,"h":82},{"id":"d8b58e7bb50780ea","type":"link out","z":"1be556fa6eb0cb07","g":"5d4be9cd3f2fb5a7","name":"EggCount","links":["d9f75986.4bd638","254a1bc5.a62c34"],"x":1755,"y":80,"wires":[]},{"id":"3947e94b6414de44","type":"inject","z":"1be556fa6eb0cb07","g":"5d4be9cd3f2fb5a7","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"900","crontab":"","once":true,"onceDelay":"5","topic":"","payload":"","payloadType":"date","x":1090,"y":80,"wires":[["47158d87bc0bc360"]]},{"id":"600fa4ed171d49f1","type":"function","z":"1be556fa6eb0cb07","g":"5d4be9cd3f2fb5a7","name":"","func":"var data = [];\nvar date = [];\nmsg.payload.forEach(function(value) {\n    data.push(value['sum(EggQty)']);\n    date.push(value['NewDate']);\n   });\n\nmsg.payload = [{\n    series: \"\",\n    data: [data],\n    labels: date\n}];\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1620,"y":80,"wires":[["d8b58e7bb50780ea","8a878a316c65ba35"]]},{"id":"6ec0233fa9f2c446","type":"mysql","z":"1be556fa6eb0cb07","g":"5d4be9cd3f2fb5a7","mydb":"dc5d34f2.4b5a38","name":"","x":1450,"y":80,"wires":[["600fa4ed171d49f1"]]},{"id":"47158d87bc0bc360","type":"function","z":"1be556fa6eb0cb07","g":"5d4be9cd3f2fb5a7","name":"Fetch Eggs","func":"msg.topic=\"select date(Date) as NewDate,sum(EggQty) from eggsrecord WHERE Date between date_sub(now(),INTERVAL 1 WEEK) and now() group by NewDate order by NewDate\";\nmsg.payload=[msg.payload];\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1270,"y":80,"wires":[["6ec0233fa9f2c446","22fdbaa0372db89a"]]},{"id":"dc5d34f2.4b5a38","type":"MySQLdatabase","name":"","host":"192.168.2.10","port":"3306","db":"eggs","tz":"UTC","charset":"UTF8"}]

This is how the graph is currently populated:
image

2

Is it the Chart that is adding the timezone or is it like this from the DB data ?

Have you tried changing the chart setting ?
image

Otherwise .. Check out MySql's DATE_FORMAT() Function (link)
in order to format the date from the sql query

1 Like
3

As far as I can tell, the db passes only the date (select date(Date) as NewDate) in the query, so my guess is that Node Red adds the zeros, I guess to enfore the ISO format of datetime. I wish I could change the X-axis lable, but that dropdown is only available for a line chart, not a bar chart. The moment I select bar chart the x-axis choice disappears... I'll look into your link as the above is an assumption, and the issue could indeed stem from the SQL query. Thanks!

4

@UnborN , many thanks for pointing me in the right direction. The issue was indeed fixed by formatting the date at the SQL query level and passing it as-is to the chart.
image

For reference, the correct syntax for the query: SELECT DATE_FORMAT(Date,'%d/%m/%y')

1 Like
5

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