RFC3339 to Unix Timestamp

General
1

Hi all,
Trying to convert timestamp for my database.
My sensor outputs timestamp in this format 2022-03-22T06:17:28:410209Z which I believe is RFC3339 according to influxdb

image
image906×526 46.5 KB

But to my knowledge influxdb node only accepts timestamp in unix time and when trying to upload with that timestamp, I get unable to parse 'topic measurement="value" 2022-03-22T06:17:28:410209Z': bad timestamp

How can I convert this from RFC3339 to Unix Timestamp?

This is important as my sensors are wireless and they tend to drop connection but will still queue the outputs accordingly with timestamps included and send all when connected again

Here is the flow im playing around with to debug it

[{"id":"215c70b.6c8cf9","type":"change","z":"2c8ee17f.1fe80e","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"[\t   {\t       \"measurement\": msg.topic,\t       \"fields\": msg.payload,\t       \"timestamp\": msg.timestamp\t  }\t]         ","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":820,"y":300,"wires":[["e4439b3a42847739","d6929422ed15285f"]],"info":"To change the "},{"id":"88dd3939f719bca0","type":"inject","z":"2c8ee17f.1fe80e","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"},{"p":"timestamp","v":"2022-03-22T06:17:28:410209Z","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"topic","payload":"{\"measurement\":69}","payloadType":"json","x":490,"y":300,"wires":[["215c70b.6c8cf9"]]},{"id":"e4439b3a42847739","type":"debug","z":"2c8ee17f.1fe80e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1100,"y":300,"wires":[]},{"id":"d6929422ed15285f","type":"influxdb batch","z":"2c8ee17f.1fe80e","influxdb":"d32d1a0b4684e895","precision":"","retentionPolicy":"","name":"","database":"database","precisionV18FluxV20":"ms","retentionPolicyV18Flux":"","org":"Initial_Organization","bucket":"test","x":1100,"y":360,"wires":[]},{"id":"d32d1a0b4684e895","type":"influxdb","hostname":"127.0.0.1","port":"8086","protocol":"http","database":"database","name":"out","usetls":false,"tls":"","influxdbVersion":"2.0","url":"http://localhost:8086","rejectUnauthorized":true}]
2

What do you see in the debug node?

3

Also, are you trying to add a sample with the current timestamp, or with a timestamp that needs to be provided with the sample?

4

That doesn't look like a RFC3339 Timestamp! There should be no colon after the 28,

2022-03-22T06:17:28.410209Z

would be correct.

5

Assuming it's a point not a colon, something like this should provide the timestamp:

const input = "2022-03-22T06:17:28.410209Z";
const unixtime = new Date(input).valueOf();
6

I don't think it wants unix time in fact. The normal way to do this would be to use
const time = new Date(input).getTime()
and then set the resolution in the influx node to milliseconds.

7

Hi currently im not in the office with the sensors, but the msg.payload contains this

image
image1600×900 252 KB

And yes, the current timestamp property contains a sample of what the sensor provides

@ghayne
Ahh thanks for catching that. But the sensor still outputs a : . I tried inputting with the . and it returns the same error.

@jbudd
Ah yes this works assuming that the point is not a colon. Now just need to find a way to change it to a point

[{"id":"9fd92f514c43c23f","type":"change","z":"2c8ee17f.1fe80e","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"[\t   {\t       \"measurement\": msg.topic,\t       \"fields\": msg.payload,\t       \"timestamp\": msg.timestamp\t  }\t]         ","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":840,"y":560,"wires":[["40226691aec02bb3","b2be9aa117b910cf"]],"info":"To change the "},{"id":"f229dd90d606ea5e","type":"inject","z":"2c8ee17f.1fe80e","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"},{"p":"timestamp","v":"2022-03-22T06:17:28.410209Z","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"topic","payload":"{\"measurement\":69}","payloadType":"json","x":330,"y":560,"wires":[["0bd984c323e210cc"]]},{"id":"40226691aec02bb3","type":"debug","z":"2c8ee17f.1fe80e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1120,"y":560,"wires":[]},{"id":"b2be9aa117b910cf","type":"influxdb batch","z":"2c8ee17f.1fe80e","influxdb":"d32d1a0b4684e895","precision":"","retentionPolicy":"","name":"","database":"database","precisionV18FluxV20":"ms","retentionPolicyV18Flux":"","org":"Initial_Organization","bucket":"test","x":1120,"y":620,"wires":[]},{"id":"0bd984c323e210cc","type":"function","z":"2c8ee17f.1fe80e","name":"","func":"const input = msg.timestamp;\nconst unixtime = new Date(input).valueOf();\nmsg.timestamp=unixtime;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":590,"y":560,"wires":[["9fd92f514c43c23f"]]},{"id":"d32d1a0b4684e895","type":"influxdb","hostname":"127.0.0.1","port":"8086","protocol":"http","database":"database","name":"out","usetls":false,"tls":"","influxdbVersion":"2.0","url":"http://localhost:8086","rejectUnauthorized":true}]
9

What are these sensors that are sending invalid timestamps?

10

@Colin
The sensors are from a local startup so I cant really blame them. I wont say their name but you can sort of see the company name from the debug node in the image :wink:

Anyways I managed to change the colon to a period with help from this stackoverflow question

[{"id":"1fbd3937e7927ca0","type":"change","z":"2c8ee17f.1fe80e","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"[\t   {\t       \"measurement\": msg.topic,\t       \"fields\": msg.payload,\t       \"timestamp\": msg.timestamp\t  }\t]         ","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":760,"y":520,"wires":[["c66c890a2dedeaba","3645eb887c954b5b"]],"info":"To change the "},{"id":"e3cd5154ee418319","type":"inject","z":"2c8ee17f.1fe80e","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"},{"p":"timestamp","v":"2022-03-22T06:17:28:410209Z","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"topic","payload":"{\"measurement\":69}","payloadType":"json","x":250,"y":520,"wires":[["48b7c4a188773e04"]]},{"id":"c66c890a2dedeaba","type":"debug","z":"2c8ee17f.1fe80e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1040,"y":520,"wires":[]},{"id":"3645eb887c954b5b","type":"influxdb batch","z":"2c8ee17f.1fe80e","influxdb":"d32d1a0b4684e895","precision":"","retentionPolicy":"","name":"","database":"database","precisionV18FluxV20":"ms","retentionPolicyV18Flux":"","org":"Initial_Organization","bucket":"test","x":1040,"y":580,"wires":[]},{"id":"48b7c4a188773e04","type":"function","z":"2c8ee17f.1fe80e","name":"","func":"const input = msg.timestamp;\nlet t = 0\nconst result = input.replace(/:/g, match => ++t === 3 ? '.' : match)\nconst unixtime = new Date(result).valueOf();\nmsg.timestamp=unixtime;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":510,"y":520,"wires":[["1fbd3937e7927ca0","ccf3212992f4bcdb"]]},{"id":"ccf3212992f4bcdb","type":"debug","z":"2c8ee17f.1fe80e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"timestamp","targetType":"msg","statusVal":"","statusType":"auto","x":790,"y":460,"wires":[]},{"id":"d32d1a0b4684e895","type":"influxdb","hostname":"127.0.0.1","port":"8086","protocol":"http","database":"database","name":"out","usetls":false,"tls":"","influxdbVersion":"2.0","url":"http://localhost:8086","rejectUnauthorized":true}]

Only thing left to do is figure out why the sensors give me values of a few days back.... the image I took was from today.
Thanks all for all your help!

11

Likely the time in the sensor is not correct.

You did not answer the question as to whether you actually need to use the time from the sensor. If the time should be the current time then use the Influx Out node (not the batch node) and it will use the current time.

I suggest feeding back to the startup that they have not formatted the date correctly, so they can fix it.

12

Yeah sorry for not being clear.

I need to use the time from the sensor as the sensors are placed a distance away from the gateway with metal machines in between.

So when the sensors actually connect, it will send all the unsent values at once and this causes influxdb to take it in at the same time as it uses the servers current time.

Will feedback! Thank you @Colin

13

Understood, then yes, the influx batch node is the right one to use.

14

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