How to change Date from String into DateTime format

General
1

Dear all, pls. I have issue with my project in Node-RED. I am receiving JSON objects from MQTT broker

for example:
{
"deviceId": "e4c56af7-2efd-4e15-b35b-90b2161fb264",
"sensor": "temperature",
"value": 19,
"date": "2020-04-16T19:11:33.269Z"
}

But I would like to convert date which is in quotation marks into date format withou quotation because I am exporting this JSON objects into InfluxDB.

Can somebody help me how to change this string into correct DateTime format via Node-RED?

Thank you.
Karol

2

There is the Date.parse() in javascript which will give you a timestamp from a date string like yours but you can also put the string straight into the new Date()method as this will implicitly call the Date.parse()to give you a js date object. See


Johannes

3

2 nodes I did to test my construction of a payload for InfluxDB - bit rough but for for an 'out' or 'batch' InfluxDB node and construct the times differently. Note Influx expects a time as microseconds. nanoseconds.

[{"id":"faf97557.84c5e8","type":"function","z":"e9fdb3ba.b608c","name":"For InfluxDB Batch","func":"var field1Data = 10\nvar field2Data = 12\nvar field3Data = 15\nvar tag1Data = \"what is this?\"\nvar tag2Data = \"I'm a tag!\"\n\nvar payload= [{\n        measurement: \"testtable\",\n        fields: {\n            field1: field1Data,\n            field2: field2Data,\n            field3: field3Data\n        },\n        tags: {\n            tag1: tag1Data,\n            tag2: tag2Data\n        },\n        timestamp: 1585500000 * 1000 *1000 * 1000\n//        timestamp: new Date()\n        }];\nnode.warn(payload);\n//msg.measurement = \"testtable\"\nmsg.payload = payload;\n//    node.send({payload})\nreturn msg;","outputs":1,"noerr":0,"x":350,"y":140,"wires":[["2dd8c5a9.a16d5a","bbed54fe.821178"]]},{"id":"60486926.e8b088","type":"function","z":"e9fdb3ba.b608c","name":"For InfluxDB Out","func":"var field1Data = 10\nvar field2Data = 12\nvar field3Data = 15\nvar tag1Data = \"what is this?\"\nvar tag2Data = \"Agile\"\n\nvar payload= \n    [\n        [\n            {\n            data: field1Data,\n            time: new Date(\"2020-03-31T20:00:01Z\").getTime() *1000 *1000\n            },\n            {\n            tag1: tag1Data\n            }\n        ],\n        [\n            {\n            data: field2Data,\n            time: new Date(\"2020-03-31T20:30:01Z\").getTime() *1000 *1000\n            },\n            {\n            tag1: tag1Data\n            }\n        ]\n    ];\n\nnode.warn(payload);\n\nmsg.measurement = \"testtable2\"\n\nmsg.payload = payload;\n\nreturn msg;","outputs":1,"noerr":0,"x":350,"y":240,"wires":[["b308d9f4.71bb08"]]}]
4

calling msg.payload = Date.parse(msg.payload); will give you milliseconds from a string.

edit or msg.payload.date in this case.

1 Like
5

Thank you JGKK for the help.
I tried that you wrote

p = msg.payload;
msg.payload= Date.parse(p);
return msg;

but the result of this is 1587117823757 and I expected this 2020-04-16T19:11:33.269Z

6

you would have to multiply than

7

Thats why I wrote about the new Date() method. Date.parse() gives you a timestamp.
use msg.payload = new Date(msg.payload);

8

You cannot do that because it would not be valid JSON. What you need is to pass the timestamp in nanoseconds.

1 Like
9

OK, I tried `msg.payload = new Date(msg.payload);

the result was

"Fri Apr 17 2020 12:03:43 GMT+0200 (Central European Summer Time)"

but I would like to receive in this format 2020-04-16T19:11:33.269Z

10

Have a look at the documentation for Date object methods:


But you will always get a string. You will have to use a timestamp in the correct format as @borpin already explained

11

https://docs.influxdata.com/influxdb/v1.7/write_protocols/line_protocol_tutorial/#timestamp

1 Like
12

so timestamp in nano second format from a date string would be:

msg.payload = (Date.parse(msg.payload))*1e6;
13

Sorry for my issue ... but thank you for your help...

from this
msg.payload = (Date.parse(msg.payload))*1e6;
return msg;

I recived this :frowning:
1587117823757000000

14

What were you expecting? I noted an error in @JGKK

msg.payload = (Date.parse(msg.payload.date))*1e6;
15

If you are using the influx batch node then that is what it expects. The timestamp in nanosecond, probably. However it is not entirely clear to me what the Time Precision setting in the advanced settings in the node does.

16

You can specify the precision for the timestamp so InfluxDB will interpret the integer correctly. If you set the advanced options as seconds, then there is no need to multiply the result of the parse.

17

if you would set it to seconds you would have to divide and round as all the javascript date methods work with milliseconds.

1 Like
18

Are you confident that is what it means, I know it would appear to mean that the timestamp should be provided in that format, but have you actually tried it and confirmed it works? When I looked into this subject a little while back it appeared to be more complex than one might think.
If you are correct then in the case here it would be simpler to leave the timestamp in milliseconds (so don't multiply by 10^6) and set it to ms in the node.

2 Likes
19

Reasonably confident although not tested. As the link to the InfluxDB docs shows an example of setting the precision in a CURL command, I see no reason why it would be different for the Node-RED node. They are all vaid InfluxDB time precisions.

Yes my mistake, set to ms and just use the Javascript parse. If, for instance, you used Tasmota in the epoch time format, you could set the InfluxDB node to seconds and just pass the timestamp straight through.

20

Thank you to all ... I dont know how to explain my situation. I am student at university and I received task in one subject.
The task consists of 2 parts:
1.) create "senzors" in JavaScript and send random values from senzors (simulate) senzors via MQTT procotol.
It is DONE and works fine.
I am sendit this JSON objects via JSON.stringify(senzor_values)

2.) the second part of my task is to receive the JSON objects in NODE-RED, than write from JSON values into Influx DB and the last make report from this data in grafana,

It is my task from university.
Now I am new in Node-RED

a) I added MQTT in node in the flow - I am receiving data - It IS OK
b) I added JSON node and set action to always convert JSON data to JSON object
c) then I send the output into debug and into influx DB and this is NOT OK - why?
Because when is see the measurement in INflux DB I see that DateTime values from JSON which I received
{
"deviceId": "e4c56af7-2efd-4e15-b35b-90b2161fb264",
"sensor": "temperature",
"value": 19,
"date": "2020-04-16T19:11:33.269Z"
}

on INflux DB was added data as a string not as a DateTime

SHOW FIELD KEYS
name: temperature
fieldKey fieldType

date string
deviceId string
sensor string
value float

It is the reason why I need to change or convert received DateTime string from JSON into DateTime format because I am importin directly this datas/values into INfluxDB.

Could you someone help me?
Thank you.
Karol