Add local date and time to a CSV-file

General
1

My intention is to convert a measurement "luchtdruk" (pressure via MQTT) together with local date and time into a comma separated CSV file: date, time, pressure.

As you can see in the graphic I use the Moment Node with output format DD-MM-YYYY, HH: mm to generate the local date and time. The output of the Change Node shows the correct date, time and pressure. But the CSV node only outputs the pressure, so no date and no time.

I've been figuring it out for a few hours but I can't. Can anyone advise me?

forum
forum1813Ă—548 55.5 KB 

[{"id":"c01f62b6.2cf2c","type":"tab","label":"LoRa Divers","disabled":false,"info":""},{"id":"f97042a6.651e9","type":"mqtt in","z":"c01f62b6.2cf2c","name":"Luchtdruk","topic":"v3/82671@ttn/devices/98732/up","qos":"2","datatype":"json","broker":"47f2cc62.a875c4","nl":false,"rap":false,"x":80,"y":60,"wires":[["a5a107a.ab9bbf8"]]},{"id":"f492c296.fe8f9","type":"csv","z":"c01f62b6.2cf2c","name":"CSV","sep":",","hdrin":false,"hdrout":"none","multi":"one","ret":"\\n","temp":"datetime,luchtdruk","skip":"0","strings":true,"include_empty_strings":true,"include_null_values":false,"x":550,"y":60,"wires":[[]]},{"id":"33d079ae.9bb146","type":"change","z":"c01f62b6.2cf2c","name":"Change","rules":[{"t":"set","p":"payload","pt":"msg","to":"{\t\"datumtijd\": payload.datetime,\t\"luchtdruk\": payload.uplink_message.decoded_payload.luchtdruk\t}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":400,"y":60,"wires":[["f492c296.fe8f9"]]},{"id":"a5a107a.ab9bbf8","type":"moment","z":"c01f62b6.2cf2c","name":"DateTime","topic":"","input":"payload.uplink_message.received_at","inputType":"msg","inTz":"Europe/Amsterdam","adjAmount":0,"adjType":"days","adjDir":"add","format":"DD-MM-YYYY,HH:mm","locale":"nl-NL","output":"payload.datetime","outputType":"msg","outTz":"Europe/Amsterdam","x":240,"y":60,"wires":[["33d079ae.9bb146"]]},{"id":"47f2cc62.a875c4","type":"mqtt-broker","name":"Luchtdruk","broker":"eu1.cloud.thethings.network","port":"1883","clientid":"","usetls":false,"compatmode":false,"protocolVersion":"4","keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","birthMsg":{},"closeTopic":"","closeQos":"0","closePayload":"","closeMsg":{},"willTopic":"","willQos":"0","willPayload":"","willMsg":{},"sessionExpiry":""}]
2

The column names in the CSV must match the names in the object.

You have "datetime" as one of the columns in the CSV but it is not called that in the object that comes from the change node.

1 Like
3

I assumed it didn't matter what names I gave those columns, but it does. Thank you for taking a look, your help is greatly appreciated!

Now I run into another problem, the date and time are in quotation marks (image), perhaps because it is a string? Do you know how I can fix that?

datetime

4

The date is in quotes because there is a comma in the timestamp.

Your options are to change the separator to another character (like tab) OR change the format of the timestamp so that it does not include a comma.

5

In that case I choose the tab. Thanks again for your help, nice to get a response so quickly.

Today I am still a beginner but a little less beginner than yesterday :wink:

6

If the timestamp column is to be used by a computer for something (rather than being read by a person) then it is usually best to use an iso string format. To get that, in a function node, you can use let now = new Date().toISOString(). That will give you a string like 2021-05-05T14:48:00.000Z
You don't need the moment node in that case.

7

You are right Colin, but I use this method because the CSV-file is processed by a Wordpress plugin on a web server. The plugin cannot work properly with the usual timestamps such as Unix.

An additional advantage of the Moment node is that it fetches the local time (with DTS) correctly.

8

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