Hello everybody,
I have an IoT device (Airvisual Node) that provides a JSON file containing data and the Epoch time (seconds since 1970) at which the measurements were made. I record those data with the timestamp in an InfluxDB database. The problem is that the timestamp from the device is not in UTC time but in my timezone so I do not want to record it as such. How can I convert this non-UTC timestamp to a UTC one? Simply adding a fix value will not work because that will depend on the DST.
Well node-red-contrib-moment will do this for you.
I'm currently using this piece of JS code in my function nodes which is getting my offset from UTC
DSTOffset = new Date().getTimezoneOffset() * 60000
I tried but it does not seem to work or I do not use it correclty. The flow below injects 1569790800000 which is the non-UTC timestamp that represents the time Europe/Luxembourg Sep. 29, 2019 9:00:00 PM. The output stays the same despite I set input TZ = Europe/Luxembourg and output TZ = UTC.
[{"id":"218abaed.7b2546","type":"comment","z":"2833a284.9d380e","name":"1569790800000 = Non-UTC timestamp Europe/Luxembourg Sep. 29, 2019 9:00:00 PM","info":"","x":380.5,"y":473,"wires":[]},{"id":"ce5e9a25.e926e8","type":"inject","z":"2833a284.9d380e","name":"","topic":"","payload":"1569790800000","payloadType":"num","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":191.5,"y":536,"wires":[["89f4639e.dd7e","9dde7c30.9e726","14df37f7.570c08"]]},{"id":"89f4639e.dd7e","type":"moment","z":"2833a284.9d380e","name":"","topic":"","input":"payload","inputType":"msg","inTz":"Europe/Luxembourg","adjAmount":0,"adjType":"days","adjDir":"add","format":"date","locale":"en_GB","output":"","outputType":"msg","outTz":"ETC/GMT","x":385,"y":536,"wires":[["cc4568c9.6309b8","bae503ec.e9037"]]},{"id":"cc4568c9.6309b8","type":"debug","z":"2833a284.9d380e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":599,"y":595,"wires":[]}]
OK, so the issue is that injecting an integer representing a JavaScript Date object is taken as UTC no matter what. If you insert either of the following, you will get the correct output:
2019-09-29T21:00:00+02:00
2019-09-29T21:00:00
21:00 29th September 2019
In fact, that response is technically correct since Date objects are UTC by definition.
http://ecma-international.org/ecma-262/5.1/#sec-15.9.1.1
In general, it is fairly critical that you process dates as UTC wherever possible and only convert for user display or user input. Of being doing date/time calculations since very early in my career over 35 years ago and it was one of the things I learned very early on and really hasn't changed. Timezone processing is horribly complex and messy with gazillions of edge-cases which is why we need bulky libraries like moment to help.
Ok that gives an offset of 2 hours which is correct. Of course that will work only if the timezone of the IoT device is set to the same timezone as the device running Node-RED, that is the case 
.
However, that will not work around DST change time because the real offset depends on the timestamp in the IoT JSON file and not on the Node-Red time. The time in the JSON is not exactly the same time of Node-RED because the JSON is updated irregularly and I run the flow every minute. See the example below where the JSON has data/timestamp that do not change (01:55). The time offset changes during the DST time change which is not correct and the recorded time of the 2 last records is wrong, that will even overwrite past data!
So I would be happy to find a general method to convert a timestamp from one TZ to another.
Can you not fix the problem at source, and get the device to send UTC, which is ideally what it should do?
That is absolutely the correct approach. All dates generated at system level should be in UTC, it is the only way to avoid DST swapover issues.
Completely agree! The problem is that it is a commercial device, I cannot change how it writes the JSON. A workaround would be to change the timezone of the device but then it will display the wrong time on its screen... arg.
By definition, Epoch time has no timezone offset -- so I suspect that the way you converted it to a date was "helping" by showing it in your local timezone...
Either that, or the device that is sending the timestamp millis has the incorrect tz (or actual time) set. I would ensure it uses an NTP service to sync its system time, verifying that the timestamps are actually UTC, before trying to work around any perceived differences.
Can I just check how you have determined that it is sending up the local time? Show us the ticks value that it is sending, tell us what the current time actually is and what you think the ticks represent.
Below is the current JSON. My time is 16:55. So you can see that the device is set to Europe/Brussels, the local time of the data is 16:51:50. The timestamp 1569948710 = UTC Tuesday, October 1, 2019 4:51:50 PM. So that shows that the timestamp is not the UTC time .
{
"date_and_time": {
"date": "2019/10/01",
"time": "16:51:50",
"timestamp": "1569948710"
},
"measurements": {
...
},
"speed_unit": "mph",
"timezone": "Europe/Brussels"
},
"status": {
...
}
}
Yes, you are right. It is not possible to reliably convert from a timezone which may include DST to another timezone, as there are times when the ticks could represent two possible times (during the repeated hour). Have you tried contacting the supplier of the device and ask him to fix it?
Otherwise, you will need to build a flow in Node-RED that checks the incoming timestamp against the expected DST and does the conversion there. It is possible that you may still get errors around the DST switchovers but that is only two points in time so you could simply filter those out.
With all your answers, I think I have found a solution. I convert the non-UTC timestamp to a string, then I remove the TZ information at the end of the string, then I call the Date/Time Formatter of node-red-contrib-moment with input TZ = Luxembourg and output TZ = UTC, I then have a string with the correct UTC time then I can convert to a timestamp. In the flow below I try just before the DST time change and just after and it works well so this solution looks foolproof, correct 
?
[{"id":"93ba06b.eb78ff8","type":"comment","z":"2833a284.9d380e","name":"Input = 1572145260000 = Non-UTC timestamp Europe/Luxembourg October 27, 2019 3:01:00 AM","info":"","x":370,"y":1259,"wires":[]},{"id":"1138185c.2c9428","type":"inject","z":"2833a284.9d380e","name":"","topic":"","payload":"1572145260000","payloadType":"num","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":141,"y":1314,"wires":[["671c4a57.d1f9d4"]]},{"id":"671c4a57.d1f9d4","type":"moment","z":"2833a284.9d380e","name":"","topic":"","input":"payload","inputType":"msg","inTz":"ETC/GMT","adjAmount":0,"adjType":"days","adjDir":"add","format":"","locale":"en_GB","output":"","outputType":"msg","outTz":"ETC/GMT","x":352,"y":1315,"wires":[["35d30c88.b8f7f4","696ea55f.a5f09c"]]},{"id":"35d30c88.b8f7f4","type":"debug","z":"2833a284.9d380e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":519,"y":1379,"wires":[]},{"id":"696ea55f.a5f09c","type":"string","z":"2833a284.9d380e","name":"","methods":[{"name":"delRightMost","params":[{"type":"str","value":"Z"}]}],"prop":"payload","propout":"payload","object":"msg","objectout":"msg","x":540,"y":1315,"wires":[["52c27106.e2562","32aa7c54.d8c364"]]},{"id":"52c27106.e2562","type":"debug","z":"2833a284.9d380e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":719,"y":1379,"wires":[]},{"id":"32aa7c54.d8c364","type":"moment","z":"2833a284.9d380e","name":"","topic":"","input":"payload","inputType":"msg","inTz":"Europe/Luxembourg","adjAmount":0,"adjType":"days","adjDir":"add","format":"date","locale":"en_GB","output":"","outputType":"msg","outTz":"ETC/GMT","x":725,"y":1316,"wires":[["e884868c.be6478","5bf0aba3.b10d84"]]},{"id":"e884868c.be6478","type":"debug","z":"2833a284.9d380e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":925,"y":1381,"wires":[]},{"id":"5bf0aba3.b10d84","type":"function","z":"2833a284.9d380e","name":"Timestamp","func":"var d = new Date(msg.payload);\nmsg.payload = d.getTime(msg.payload);\nreturn msg;","outputs":1,"noerr":0,"x":940,"y":1315,"wires":[["2ec533c4.accccc"]]},{"id":"2ec533c4.accccc","type":"debug","z":"2833a284.9d380e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":1110,"y":1379,"wires":[]},{"id":"925e0962.c65888","type":"comment","z":"2833a284.9d380e","name":"Output = 1572141660000 = GMT October 27, 2019 2:01:00 AM","info":"","x":944,"y":1258,"wires":[]},{"id":"943d076d.bcaee8","type":"comment","z":"2833a284.9d380e","name":"Input = 1572145140 = Non-UTC timestamp Europe/Luxembourg October 27, 2019 2:59:00 AM","info":"","x":358,"y":1439,"wires":[]},{"id":"4272e3b9.45b5ec","type":"comment","z":"2833a284.9d380e","name":"Output = 1572137940000 = GMT October 27, 2019 12:59:00 AM","info":"","x":922,"y":1439,"wires":[]},{"id":"ca9eb40b.099a38","type":"inject","z":"2833a284.9d380e","name":"","topic":"","payload":"1572145140000","payloadType":"num","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":135,"y":1492,"wires":[["671c4a57.d1f9d4"]]}]
Maybe I'm being daft here... but if I take the epoch seconds, mutiply by 1000 to get ms, and create a new Date from it, I get the same Zulu time that you are seeing as local time?!?
> new Date(1569948710000)
2019-10-01T16:51:50.000Z
My timezone is EDT -04:00, so it's clearly NOT using my timezone to create the date. If I show the date in my local string format, I get:
> new Date(1569948710000).toLocaleString()
'2019-10-1 12:51:50'
So I suspect the JSON info sent from the device is indeed epoch secs (UTC), but the other timezone field is just information about where the device is located -- in case you need to convert the Zulu timestamp to that local timezone for display purposes. In this case, I would use the moment node to do the conversion, instead of hacking around with timezone offsets -- for the exact reasons brought up by Colin and Julian and others.
But again, please do NOT try to massage the epoch time into some "time-shifted" version that you end up storing in a database. That way leads to disaster...
That conversion is correct. The problem is that the actual local time was 16:55 so the actual UTC time was not 16:51 but an hour different. So the timestamp integer should have been 1 hour different from the value passed.
I confirm what Colin well said in his last post, the timestamp provided by the device does not represent the UTC time and that is the root cause of the problem. Sorry if it was not clear. I will contact the supplier anyway to fix the issue but I do not expect a quick fix, the firmware has not been updated since I got the device few years ago...
Sadly I suspect the supplier will say that users want it in local time so the device is operating according to spec.
Sadly indeed! But I suspect Colin is right... too many vendors think only in local time.
BUT, if you have the "local" timestamp in epoch secs, AND you have the timezone name, can't the moment node do the adjustment properly? Maybe that's a question for Julian: can moment use an incoming msg property to set the incoming timezone?
Copyright OpenJS Foundation and Node-RED contributors. All rights reserved. The OpenJS Foundation has registered trademarks and uses trademarks. For a list of trademarks of the OpenJS Foundation, please see our Trademark Policy and Trademark List. Trademarks and logos not indicated on the list of OpenJS Foundation trademarks are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.
The OpenJS Foundation | Terms of Use | Privacy Policy | OpenJS Foundation Bylaws | Trademark Policy | Trademark List | Cookie Policy