Convert format of datetime from database to local time with Moment node

Greetings,

I had tried to use the moment node to convert the datetime from a mysql database to local time and it works. Now I am trying to convert for an array of datetime with moment node. My database was storing the reading of temperature and humidity and the datetime. So, I make a dropdown for the user to choose the date and it will extract the data from database. I've tried a simple method to make a for loop and pushing each data to the moment node, but the output only one. Hope someone can help me to solve this problem, thank you in advance.

Best Regards.

My flow :
[{"id":"7f4187e7.18f708","type":"inject","z":"ecd7ee06.58772","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":220,"y":700,"wires":[["649e33b6.df01fc"]]},{"id":"649e33b6.df01fc","type":"function","z":"ecd7ee06.58772","name":"","func":"msg.payload = [28.8,\"2020-05-27T08:48:37.000Z\",28.7,\"2020-05-27T08:48:38.000Z\",28.6,\"2020-05-27T08:48:39.000Z\",28.6,\"2020-05-27T09:14:37.000Z\"];\nreturn msg;","outputs":1,"noerr":0,"x":370,"y":700,"wires":[["a3c16d89.78afb"]]},{"id":"b789b9ed.2c1ac8","type":"moment","z":"ecd7ee06.58772","name":"","topic":"","input":"payload","inputType":"msg","inTz":"Europe/Oslo","adjAmount":0,"adjType":"days","adjDir":"add","format":"DD-MM-YYYY HH:mm:ss","locale":"en_US","output":"payload","outputType":"msg","outTz":"Asia/Kuala_Lumpur","x":740,"y":700,"wires":[["fd9995aa.4f9e28"]]},{"id":"a3c16d89.78afb","type":"function","z":"ecd7ee06.58772","name":"","func":"var arr=[];\nvar index = 1;\nvar myarr=msg.payload;\nfor (i = 0; i < myarr.length; i++)\n{\n msg.payload = myarr[index];\n index+=2;\n return msg;\n}\n","outputs":1,"noerr":0,"x":530,"y":700,"wires":[["b789b9ed.2c1ac8"]]},{"id":"fd9995aa.4f9e28","type":"debug","z":"ecd7ee06.58772","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":990,"y":700,"wires":[]}]

Can I ask why you need to do this? Often one would only do such conversion at the point of display.

Yes, I want to show these data with a line chart. But because of the datetime format get from database to node red will be something like "2020-05-27T08:48:37.000Z" which is not the correct format for the chart.

That is standard ISO standard, you shouldnt need moment to convert that to millisecs epoch. The standard javascript date functions can do that.

1 Like

You can create a javascript Date from that using the Date constructor and then use getTicks() to convert it to ticks for feeding to the chart . Something like this
msg.payload = new Date(msg.payload).getTime()

[{"id":"56b18f59.c97908","type":"inject","z":"bdd7be38.d3b55","name":"2020-05-27T08:48:37.000Z","topic":"","payload":"2020-05-27T08:48:37.000Z","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":158,"y":1403,"wires":[["2bed9c3d.5da2e4"]]},{"id":"2bed9c3d.5da2e4","type":"function","z":"bdd7be38.d3b55","name":"Create Ticks from payload","func":"msg.payload = new Date(msg.payload).getTime()\nreturn msg;","outputs":1,"noerr":0,"x":417,"y":1402,"wires":[["3eaa586e.b3a17"]]},{"id":"3eaa586e.b3a17","type":"debug","z":"bdd7be38.d3b55","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":628,"y":1402,"wires":[]}]
1 Like

Wow great, this is simple and helpful. Thank you @Colin. :+1:

1 Like

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