Change Time Format string value

[Ward]

Hi,

Little background. I wrote a flow where i store all the alarms in a csv file. And every time the csv file is updated i read the new alarms and write only those to InfluxDB. Everything works except, i want to use the time written to the csv file (col14). So you can see the different format, i wrote the time from the csv file in the tag column and the time format i need in the timestamp column. Which you can see in the debug screen. (picture below)

I search the forum and the internet for a solution but didn't find anything. Except the node moment but because i need to do it in a function node (program next debug screen) i can't use a external node or can you call a external node from within a loop?

Thanks at advance!
Kind regards,
Ward

PS here is the whole flow

[{"id":"b636f796.281928","type":"switch","z":"5a137ad7.f92bd4","name":"Check Bierkelder alarmlog change","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"C:\\Users\\Administrator\\Documents\\Logfiles Alarmen HMI's\\Proceskamer Bierkelder\\Alarm_log_Test.csv","vt":"str"}],"checkall":"true","repair":false,"outputs":1,"x":500,"y":360,"wires":[["7759b3f4.77b7fc"]]},{"id":"38988be5.525ec4","type":"watch","z":"5a137ad7.f92bd4","name":"Logfiles Proceskamer Bierkelder","files":"C:\\Users\\Administrator\\Documents\\Logfiles Alarmen HMI's\\Proceskamer Bierkelder\\","recursive":true,"x":150,"y":360,"wires":[["b636f796.281928"]]},{"id":"7759b3f4.77b7fc","type":"file in","z":"5a137ad7.f92bd4","name":"Alarmen Bierkelder csv file","filename":"C:\\Users\\Administrator\\Documents\\Logfiles Alarmen HMI's\\Proceskamer Bierkelder\\Alarm_log_Test.csv","format":"utf8","chunk":false,"sendError":false,"encoding":"none","x":840,"y":360,"wires":[["dca5b0f.49d0f5"]]},{"id":"dca5b0f.49d0f5","type":"csv","z":"5a137ad7.f92bd4","name":"Seperate csv through semicolon","sep":";","hdrin":false,"hdrout":"","multi":"mult","ret":"\\r\\n","temp":"","skip":"0","strings":false,"x":1170,"y":360,"wires":[["dcf807d6.1971c8"]]},{"id":"dcf807d6.1971c8","type":"function","z":"5a137ad7.f92bd4","name":"Copy payload","func":"msg.payloadFile = msg.payload;\nreturn msg;","outputs":1,"noerr":0,"x":1500,"y":360,"wires":[["4426d49b.8daedc"]]},{"id":"3c29370c.ede8a8","type":"function","z":"5a137ad7.f92bd4","name":"Search last written line to InfluxDB","func":"//msg.payloadFile = msg.payload;\nmsg.payloadFileLength = msg.payloadFile.length;\n\nvar i = 1;\nvar x= msg.payload;\nvar y;\nvar found = 0;\n\nwhile (found === 0) {\n  y = msg.payloadFile[msg.payloadFileLength - i];  \n  if (x === JSON.stringify(y)) found = 1;\n  msg.NewAlarms = i - 1;\n  i++;\n  if (i === msg.payloadFileLength) found = 1;\n}\n\nmsg.last = msg.payloadFile[msg.payloadFileLength -1];\nmsg.payload = x;\nreturn msg;","outputs":1,"noerr":0,"x":1180,"y":420,"wires":[["bfaa47e9.cb7af8","f605b276.91a3d","870c47c.97e0eb8"]]},{"id":"4426d49b.8daedc","type":"file in","z":"5a137ad7.f92bd4","name":"Last Alarm Bierkelder txt file","filename":"C:\\Users\\Administrator\\Documents\\Logfiles Alarmen HMI's\\Proceskamer Bierkelder\\Alarm_log_10_Last.txt","format":"utf8","chunk":false,"sendError":false,"encoding":"none","x":840,"y":420,"wires":[["3c29370c.ede8a8"]]},{"id":"b5eff9b9.6a05e8","type":"inject","z":"5a137ad7.f92bd4","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":"10","x":580,"y":420,"wires":[["7759b3f4.77b7fc"]]},{"id":"94f08eb3.57588","type":"file","z":"5a137ad7.f92bd4","name":"","filename":"C:\\Users\\Administrator\\Documents\\Logfiles Alarmen HMI's\\Proceskamer Bierkelder\\Alarm_log_10_Last.txt","appendNewline":false,"createDir":false,"overwriteFile":"true","encoding":"none","x":2140,"y":420,"wires":[["eb854a4.778eeb8"]]},{"id":"eb854a4.778eeb8","type":"debug","z":"5a137ad7.f92bd4","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":2610,"y":420,"wires":[]},{"id":"bfaa47e9.cb7af8","type":"function","z":"5a137ad7.f92bd4","name":"Copy last Messagge in msg.payload","func":"msg.payload = msg.last;\nreturn msg;","outputs":1,"noerr":0,"x":1570,"y":420,"wires":[["94f08eb3.57588"]]},{"id":"f605b276.91a3d","type":"debug","z":"5a137ad7.f92bd4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"NewAlarms","targetType":"msg","x":1520,"y":480,"wires":[]},{"id":"870c47c.97e0eb8","type":"function","z":"5a137ad7.f92bd4","name":"Alarmen wegschrijven in Array","func":"var i;\nvar Alarmen = [];\nvar FieldValue1;\nvar FieldValue2;\nvar FieldValue3;\nvar FieldValue4;\nvar FieldValue5;\nvar Tagvalue1;\n\nfor (i = 0; msg.NewAlarms !== 0; i++) {\n    FieldValue1 = msg.payloadFile[msg.payloadFileLength - msg.NewAlarms].col2;\n    FieldValue2 = msg.payloadFile[msg.payloadFileLength - msg.NewAlarms].col3;\n    FieldValue3 = msg.payloadFile[msg.payloadFileLength - msg.NewAlarms].col4;\n    FieldValue4 = msg.payloadFile[msg.payloadFileLength - msg.NewAlarms].col5;\n    FieldValue5 = msg.payloadFile[msg.payloadFileLength - msg.NewAlarms].col15;\n    Tagvalue1 = msg.payloadFile[msg.payloadFileLength - msg.NewAlarms].col14;\n     var dt = new Date();\n         dt.setSeconds( dt.getMilliseconds() + i );\n    Alarmen [i] = {\n        measurement: 'Proceskamer_Bierkelder_Alarmen',\n        fields: {\n            MsgProc: FieldValue1,\n            StateAfter: FieldValue2,\n            MsgClass: FieldValue3,\n            MsgNumber: FieldValue4,\n            MsgText: FieldValue5\n        },\n        tags: {\n            time: Tagvalue1\n        },\n        timestamp: dt\n    }\n\n    msg.NewAlarms--;\n}\nmsg.payload = Alarmen;\nreturn msg;","outputs":1,"noerr":0,"x":1170,"y":520,"wires":[["ec9d8848.045fc8","5fb36f65.27d87"]]},{"id":"ec9d8848.045fc8","type":"debug","z":"5a137ad7.f92bd4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1510,"y":520,"wires":[]},{"id":"5fb36f65.27d87","type":"influxdb batch","z":"5a137ad7.f92bd4","influxdb":"3c34d289.ea64de","precision":"","retentionPolicy":"","name":"InfluxDB Test","x":1510,"y":560,"wires":[]},{"id":"3c34d289.ea64de","type":"influxdb","z":"","hostname":"127.0.0.1","port":"8086","protocol":"http","database":"Test","name":"TestDB","usetls":false,"tls":"d05a2116.ca1dd"},{"id":"d05a2116.ca1dd","type":"tls-config","z":"","name":"local-tls","cert":"","key":"","ca":"","certname":"","keyname":"","caname":"","verifyservercert":false}]

[janvda]

@Ward

Everything works except, i want to use the time written to the csv file (col14)

It is not exactly clear for me which changes to the output message you want.

[Ward]

@janvda if i want to write data to my InfluxDB i think it needs the format of the timestamp as "yyyy-MM-ddThh:mm:ss.msZ" (this is the format i always use and works). But the format from the csv file; wich is the timestamp when my alarm is generated; is in "dd/MM/yyyy hh:mm:ss".

So i have to change the format.
I hope it explains it a little better?

[Ward]

I forgot to tell in the original post but the problem is that the day and the month is switched. If i use the time string from the csv file the program don't recognize the time format (first picture) but if i use the string format from the second picture it works like a charm.

So if i know how to read the time string i can create the correct time format.But now i don't know how

[Colin]

If it helps you can use a javascript Date object for the timestamp in the Influx nodes. So if you can get it into a Date object then you don't need to do any more.

[Ward]

hi colin.

I'm new to javascript and learning on the go so my apologies if it is a stupid question. But what do you mean with a date object?

[Colin]

Google (other search engines are available) is your friend.

JavaScript Date Objects

[janvda]

@Ward

@janvda if i want to write data to my InfluxDB i think it needs the format of the timestamp as "yyyy-MM-ddThh:mm:ss.msZ" (this is the format i always use and works). But the format from the csv file; wich is the timestamp when my alarm is generated; is in "dd/MM/yyyy hh:mm:ss".

Here is the jsonata expression that converts it to the proper format.

https://try.jsonata.org/BHdJk8Hom

payload~>$toMillis('[M01]/[D01]/[Y0001] [H#01]:[m01]:[s01]')~>$fromMillis()

So instead of a function node - I would do the mapping in jsonata expression in a node-red change node.
For the conversion of the timestamp you can reuse above jsonata expression.

If you share an input example in text format + corresponding output then me or someone else might provide the complete jsonata expression that does this mapping.

kr
Jan.

[Ward]

@Colin, @janvda
oke i will look deeper in the topics first.
Thanks for the quick reply's.

[TotallyInformation]

@Ward

i store all the alarms in a csv file. And every time the csv file is updated i read the new alarms and write only those to InfluxDB

Somewhat of a tangent - but why not write the alarm to InfluxDB at the same time you are writing to CSV? Then you wouldn't need to mess with the time at all since InfluxDB will add it for you.

[Ward]

@TotallyInformation
Because the csv file is automatically created by a siemens panel. And because we don't want to map all the alarms we want to read the file itself.

And the time option is in case the connection breaks. The panel will still log all the alarms and after the connection restore we can recover all the data.