# Modify datetime in array for proper chart ui output

**URL:** <https://discourse.nodered.org/t/modify-datetime-in-array-for-proper-chart-ui-output/57287>\
**Category:** Dashboard\
**Created:** [26 January 2022 20:14 UTC](https://discourse.nodered.org/t/modify-datetime-in-array-for-proper-chart-ui-output/57287 "2022-01-26T20:14:44Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![mfassier](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mfassier/32/48013_2.png) [@mfassier](https://discourse.nodered.org/u/mfassier)\
**Post date:** [26 January 2022 20:14 UTC](https://discourse.nodered.org/t/modify-datetime-in-array-for-proper-chart-ui-output/57287/1 "2022-01-26T20:14:44Z")

</div>

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 ?

```auto
[{"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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/c/2c1944acaaaba21a49be4707f0d958a41e6c2fbe.png)

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [26 January 2022 20:51 UTC](https://discourse.nodered.org/t/modify-datetime-in-array-for-proper-chart-ui-output/57287/2 "2022-01-26T20:51:29Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/5/45a5016bf51a603077dc086a50221544a98b0959.png)

Otherwise .. Check out MySql's **DATE\_FORMAT()** Function ([link](https://www.w3schools.com/sql/func_mysql_date_format.asp))  
in order to format the date from the sql query

---

<div class="post-metadata">

**Author:** ![mfassier](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mfassier/32/48013_2.png) [@mfassier](https://discourse.nodered.org/u/mfassier)\
**Post date:** [26 January 2022 20:57 UTC](https://discourse.nodered.org/t/modify-datetime-in-array-for-proper-chart-ui-output/57287/3 "2022-01-26T20:57:06Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![mfassier](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mfassier/32/48013_2.png) [@mfassier](https://discourse.nodered.org/u/mfassier)\
**Post date:** [26 January 2022 21:50 UTC](https://discourse.nodered.org/t/modify-datetime-in-array-for-proper-chart-ui-output/57287/4 "2022-01-26T21:50:58Z")

</div>

@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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/f/6f6de37900622d7f3b156542548261c13e1f9326.png)

For reference, the correct syntax for the query: **SELECT DATE\_FORMAT(Date,'%d/%m/%y')**

---

<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:** [9 February 2022 21:51 UTC](https://discourse.nodered.org/t/modify-datetime-in-array-for-proper-chart-ui-output/57287/5 "2022-02-09T21:51:43Z")

</div>

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