Timestamps not being recorded correctly

I have a small flow that gets weather data from the met office, which I then change into the required format for feeding into the node-red-contrib-influxdb node. This then updates the data in Influxdb (which is on the same cloud server).
The flow obtains a 48hr hourly future forecast split into hourly intervals.

myflow

I've created a simple flow (just a inject, function & debug node) to replicate the actual data, and how it's formatted, and also attached a csv output (as a pdf) from influxdb to show what is being recorded when that data is sent to influx via a node-red-contrib-influxdb node node.
(The actual flow data has been edited, and vastly reduced in size for ease of forum reading)

The problem that I'm having is that although the timestamps in the data look ok before entering the 'influx out' node, I'm seeing discrepancies in the timestamps when looking at the data in influx.
For example, looking at the timestamp from the flow for the first array it shows time: 2023-04-23T00:00:00.000Z, but when reordered in influx, the timestamp is shown as 2023-04-23T00:08:00Z - 8 minutes late!

I first thought that it may of been a timezone issue, but checking the other array timestamps, they are not all 8 minutes late, and the delay varies.

influxdb_data.pdf (14.1 KB)

[{"id":"c9eebc45e8041db3","type":"debug","z":"03cd1d2be26baaa9","name":"debug 12","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":540,"y":460,"wires":[]},{"id":"248dd98f49b18b13","type":"function","z":"03cd1d2be26baaa9","name":"format data","func":"let fullArray = msg.payload\nconst tempArray = []\nfor (let i = 0; i < fullArray.length; i++) {\n    let a = (fullArray[i])\n    const ts = new Date(a.time).getTime()\n    const tempData = [{\n        time: ts,\n        temperature: a.screenTemperature,\n        maxTemp: a.maxScreenAirTemp,\n        minTemp: a.minScreenAirTemp,\n        feelsLike: a.feelsLikeTemperature,\n        windGust: Math.round(a.windGustSpeed10m / 1609.344 * 3600),\n        windSpeed: Math.round(a.windSpeed10m / 1609.344 * 3600),\n        rainProb: a.probOfPrecipitation,\n        rainPrecip: a.totalPrecipAmount,\n        weatherCode: a.significantWeatherCode,\n        uvIndex: a.uvIndex\n      },\n      {\n        tag1: 'weather',\n        tag2: 'metoffice'\n      }]\n        tempArray.push(tempData)\n}\nmsg.payload = tempArray\nreturn msg","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":390,"y":460,"wires":[["c9eebc45e8041db3"]]},{"id":"6ffe6bf821feb16f","type":"inject","z":"03cd1d2be26baaa9","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"time\":\"2023-04-23T00:00Z\",\"screenTemperature\":8.24,\"maxScreenAirTemp\":8.5,\"minScreenAirTemp\":8.24,\"screenDewPointTemperature\":5.91,\"feelsLikeTemperature\":7.38,\"windSpeed10m\":1.61,\"windDirectionFrom10m\":171,\"windGustSpeed10m\":3.19,\"max10mWindGust\":4.59,\"visibility\":7912,\"screenRelativeHumidity\":85.39,\"mslp\":100244,\"uvIndex\":0,\"significantWeatherCode\":12,\"precipitationRate\":0.66,\"totalPrecipAmount\":0.13,\"totalSnowAmount\":0,\"probOfPrecipitation\":56},{\"time\":\"2023-04-23T01:00Z\",\"screenTemperature\":8.02,\"maxScreenAirTemp\":8.24,\"minScreenAirTemp\":8,\"screenDewPointTemperature\":6.31,\"feelsLikeTemperature\":7.34,\"windSpeed10m\":1.21,\"windDirectionFrom10m\":165,\"windGustSpeed10m\":2.54,\"max10mWindGust\":4.49,\"visibility\":10562,\"screenRelativeHumidity\":89.23,\"mslp\":100190,\"uvIndex\":0,\"significantWeatherCode\":12,\"precipitationRate\":0.22,\"totalPrecipAmount\":0.24,\"totalSnowAmount\":0,\"probOfPrecipitation\":48},{\"time\":\"2023-04-23T02:00Z\",\"screenTemperature\":7.92,\"maxScreenAirTemp\":8.02,\"minScreenAirTemp\":7.91,\"screenDewPointTemperature\":6.38,\"feelsLikeTemperature\":7.19,\"windSpeed10m\":1.42,\"windDirectionFrom10m\":160,\"windGustSpeed10m\":2.65,\"max10mWindGust\":3.41,\"visibility\":11772,\"screenRelativeHumidity\":90.33,\"mslp\":100150,\"uvIndex\":0,\"significantWeatherCode\":8,\"precipitationRate\":0,\"totalPrecipAmount\":0,\"totalSnowAmount\":0,\"probOfPrecipitation\":13},{\"time\":\"2023-04-23T03:00Z\",\"screenTemperature\":7.74,\"maxScreenAirTemp\":7.92,\"minScreenAirTemp\":7.73,\"screenDewPointTemperature\":6.43,\"feelsLikeTemperature\":7.03,\"windSpeed10m\":1.28,\"windDirectionFrom10m\":96,\"windGustSpeed10m\":2.43,\"max10mWindGust\":3.23,\"visibility\":12127,\"screenRelativeHumidity\":91.59,\"mslp\":100114,\"uvIndex\":0,\"significantWeatherCode\":8,\"precipitationRate\":0,\"totalPrecipAmount\":0,\"totalSnowAmount\":0,\"probOfPrecipitation\":12},{\"time\":\"2023-04-23T04:00Z\",\"screenTemperature\":7.71,\"maxScreenAirTemp\":7.75,\"minScreenAirTemp\":7.7,\"screenDewPointTemperature\":6.46,\"feelsLikeTemperature\":7,\"windSpeed10m\":1.26,\"windDirectionFrom10m\":81,\"windGustSpeed10m\":2.31,\"max10mWindGust\":2.95,\"visibility\":11349,\"screenRelativeHumidity\":92.15,\"mslp\":100100,\"uvIndex\":0,\"significantWeatherCode\":8,\"precipitationRate\":0,\"totalPrecipAmount\":0,\"totalSnowAmount\":0,\"probOfPrecipitation\":17},{\"time\":\"2023-04-23T05:00Z\",\"screenTemperature\":7.72,\"maxScreenAirTemp\":7.74,\"minScreenAirTemp\":7.71,\"screenDewPointTemperature\":6.59,\"feelsLikeTemperature\":7.01,\"windSpeed10m\":1.37,\"windDirectionFrom10m\":73,\"windGustSpeed10m\":2.43,\"max10mWindGust\":2.93,\"visibility\":10175,\"screenRelativeHumidity\":92.64,\"mslp\":100093,\"uvIndex\":1,\"significantWeatherCode\":8,\"precipitationRate\":0,\"totalPrecipAmount\":0,\"totalSnowAmount\":0,\"probOfPrecipitation\":15},{\"time\":\"2023-04-23T06:00Z\",\"screenTemperature\":7.85,\"maxScreenAirTemp\":7.88,\"minScreenAirTemp\":7.72,\"screenDewPointTemperature\":6.61,\"feelsLikeTemperature\":7.02,\"windSpeed10m\":1.71,\"windDirectionFrom10m\":303,\"windGustSpeed10m\":2.78,\"max10mWindGust\":3,\"visibility\":11935,\"screenRelativeHumidity\":92.1,\"mslp\":100087,\"uvIndex\":1,\"significantWeatherCode\":8,\"precipitationRate\":0,\"totalPrecipAmount\":0,\"totalSnowAmount\":0,\"probOfPrecipitation\":13},{\"time\":\"2023-04-23T07:00Z\",\"screenTemperature\":8.19,\"maxScreenAirTemp\":8.2,\"minScreenAirTemp\":7.85,\"screenDewPointTemperature\":6.53,\"feelsLikeTemperature\":7.26,\"windSpeed10m\":1.85,\"windDirectionFrom10m\":310,\"windGustSpeed10m\":3.14,\"max10mWindGust\":3.14,\"visibility\":11684,\"screenRelativeHumidity\":89.74,\"mslp\":100100,\"uvIndex\":1,\"significantWeatherCode\":8,\"precipitationRate\":0,\"totalPrecipAmount\":0,\"totalSnowAmount\":0,\"probOfPrecipitation\":12},{\"time\":\"2023-04-23T08:00Z\",\"screenTemperature\":8.68,\"maxScreenAirTemp\":8.7,\"minScreenAirTemp\":8.19,\"screenDewPointTemperature\":6.48,\"feelsLikeTemperature\":7.91,\"windSpeed10m\":1.81,\"windDirectionFrom10m\":326,\"windGustSpeed10m\":3.25,\"max10mWindGust\":3.25,\"visibility\":12758,\"screenRelativeHumidity\":86.37,\"mslp\":100087,\"uvIndex\":2,\"significantWeatherCode\":8,\"precipitationRate\":0,\"totalPrecipAmount\":0,\"totalSnowAmount\":0,\"probOfPrecipitation\":11},{\"time\":\"2023-04-23T09:00Z\",\"screenTemperature\":9.25,\"maxScreenAirTemp\":9.29,\"minScreenAirTemp\":8.68,\"screenDewPointTemperature\":6.24,\"feelsLikeTemperature\":8.19,\"windSpeed10m\":2.23,\"windDirectionFrom10m\":319,\"windGustSpeed10m\":3.84,\"max10mWindGust\":3.84,\"visibility\":12550,\"screenRelativeHumidity\":81.91,\"mslp\":100110,\"uvIndex\":2,\"significantWeatherCode\":8,\"precipitationRate\":0,\"totalPrecipAmount\":0,\"totalSnowAmount\":0,\"probOfPrecipitation\":14},{\"time\":\"2023-04-23T10:00Z\",\"screenTemperature\":9.73,\"maxScreenAirTemp\":9.87,\"minScreenAirTemp\":9.25,\"screenDewPointTemperature\":6.22,\"feelsLikeTemperature\":8.29,\"windSpeed10m\":2.81,\"windDirectionFrom10m\":322,\"windGustSpeed10m\":4.85,\"max10mWindGust\":4.85,\"visibility\":10440,\"screenRelativeHumidity\":79.66,\"mslp\":100103,\"uvIndex\":3,\"significantWeatherCode\":8,\"precipitationRate\":0,\"totalPrecipAmount\":0,\"totalSnowAmount\":0,\"probOfPrecipitation\":16},{\"time\":\"2023-04-23T11:00Z\",\"screenTemperature\":10.42,\"maxScreenAirTemp\":10.45,\"minScreenAirTemp\":9.73,\"screenDewPointTemperature\":6.08,\"feelsLikeTemperature\":9.03,\"windSpeed10m\":2.89,\"windDirectionFrom10m\":328,\"windGustSpeed10m\":5.31,\"max10mWindGust\":5.71,\"visibility\":10787,\"screenRelativeHumidity\":75.55,\"mslp\":100090,\"uvIndex\":3,\"significantWeatherCode\":8,\"precipitationRate\":0,\"totalPrecipAmount\":0,\"totalSnowAmount\":0,\"probOfPrecipitation\":16},{\"time\":\"2023-04-23T12:00Z\",\"screenTemperature\":11.13,\"maxScreenAirTemp\":11.19,\"minScreenAirTemp\":10.42,\"screenDewPointTemperature\":6.1,\"feelsLikeTemperature\":9.54,\"windSpeed10m\":3.45,\"windDirectionFrom10m\":336,\"windGustSpeed10m\":6.31,\"max10mWindGust\":6.54,\"visibility\":12018,\"screenRelativeHumidity\":72.35,\"mslp\":100081,\"uvIndex\":4,\"significantWeatherCode\":7,\"precipitationRate\":0,\"totalPrecipAmount\":0,\"totalSnowAmount\":0,\"probOfPrecipitation\":12},{\"time\":\"2023-04-23T13:00Z\",\"screenTemperature\":10.94,\"maxScreenAirTemp\":11.13,\"minScreenAirTemp\":10.9,\"screenDewPointTemperature\":6,\"feelsLikeTemperature\":9.14,\"windSpeed10m\":3.85,\"windDirectionFrom10m\":346,\"windGustSpeed10m\":7.07,\"max10mWindGust\":7.65,\"visibility\":16886,\"screenRelativeHumidity\":73.12,\"mslp\":100081,\"uvIndex\":3,\"significantWeatherCode\":8,\"precipitationRate\":0,\"totalPrecipAmount\":0,\"totalSnowAmount\":0,\"probOfPrecipitation\":16},{\"time\":\"2023-04-23T14:00Z\",\"screenTemperature\":10.82,\"maxScreenAirTemp\":10.94,\"minScreenAirTemp\":10.78,\"screenDewPointTemperature\":6.09,\"feelsLikeTemperature\":8.82,\"windSpeed10m\":4.29,\"windDirectionFrom10m\":351,\"windGustSpeed10m\":7.92,\"max10mWindGust\":8.94,\"visibility\":12577,\"screenRelativeHumidity\":74.09,\"mslp\":100071,\"uvIndex\":3,\"significantWeatherCode\":12,\"precipitationRate\":0.33,\"totalPrecipAmount\":0.24,\"totalSnowAmount\":0,\"probOfPrecipitation\":46},{\"time\":\"2023-04-23T15:00Z\",\"screenTemperature\":10.67,\"maxScreenAirTemp\":10.82,\"minScreenAirTemp\":10.62,\"screenDewPointTemperature\":5.95,\"feelsLikeTemperature\":8.47,\"windSpeed10m\":4.72,\"windDirectionFrom10m\":353,\"windGustSpeed10m\":8.43,\"max10mWindGust\":8.98,\"visibility\":13916,\"screenRelativeHumidity\":73.62,\"mslp\":100084,\"uvIndex\":2,\"significantWeatherCode\":12,\"precipitationRate\":0.46,\"totalPrecipAmount\":0.51,\"totalSnowAmount\":0,\"probOfPrecipitation\":45},{\"time\":\"2023-04-23T16:00Z\",\"screenTemperature\":10.59,\"maxScreenAirTemp\":10.67,\"minScreenAirTemp\":10.51,\"screenDewPointTemperature\":5.65,\"feelsLikeTemperature\":8.42,\"windSpeed10m\":4.63,\"windDirectionFrom10m\":349,\"windGustSpeed10m\":8.23,\"max10mWindGust\":9.19,\"visibility\":15869,\"screenRelativeHumidity\":72.69,\"mslp\":100099,\"uvIndex\":2,\"significantWeatherCode\":7,\"precipitationRate\":0,\"totalPrecipAmount\":0,\"totalSnowAmount\":0,\"probOfPrecipitation\":16},{\"time\":\"2023-04-23T17:00Z\",\"screenTemperature\":10.3,\"maxScreenAirTemp\":10.59,\"minScreenAirTemp\":10.26,\"screenDewPointTemperature\":5.72,\"feelsLikeTemperature\":8.35,\"windSpeed10m\":4.07,\"windDirectionFrom10m\":351,\"windGustSpeed10m\":7.55,\"max10mWindGust\":9.03,\"visibility\":25394,\"screenRelativeHumidity\":74.09,\"mslp\":100136,\"uvIndex\":1,\"significantWeatherCode\":7,\"precipitationRate\":0,\"totalPrecipAmount\":0,\"totalSnowAmount\":0,\"probOfPrecipitation\":15},{\"time\":\"2023-04-23T18:00Z\",\"screenTemperature\":10.05,\"maxScreenAirTemp\":10.3,\"minScreenAirTemp\":10.02,\"screenDewPointTemperature\":5.37,\"feelsLikeTemperature\":7.77,\"windSpeed10m\":4.88,\"windDirectionFrom10m\":335,\"windGustSpeed10m\":8.74,\"max10mWindGust\":10.94,\"visibility\":20188,\"screenRelativeHumidity\":73.26,\"mslp\":100175,\"uvIndex\":1,\"significantWeatherCode\":3,\"precipitationRate\":0,\"totalPrecipAmount\":0,\"totalSnowAmount\":0,\"probOfPrecipitation\":9},{\"time\":\"2023-04-23T19:00Z\",\"screenTemperature\":9,\"maxScreenAirTemp\":10.05,\"minScreenAirTemp\":8.98,\"screenDewPointTemperature\":5.44,\"feelsLikeTemperature\":6.41,\"windSpeed10m\":5.09,\"windDirectionFrom10m\":340,\"windGustSpeed10m\":9.18,\"max10mWindGust\":11.56,\"visibility\":18090,\"screenRelativeHumidity\":78.6,\"mslp\":100260,\"uvIndex\":1,\"significantWeatherCode\":7,\"precipitationRate\":0,\"totalPrecipAmount\":0,\"totalSnowAmount\":0,\"probOfPrecipitation\":10}]","payloadType":"json","x":250,"y":460,"wires":[["248dd98f49b18b13"]]}]

That does indeed seem very odd. I can't test it as I don't use influxdb 2. Are you using the latest version of Influxdb? I think early versions had a few bugs. Similarly are you using the latest version of the node?

It might be worth trying, as an experiment, subtracting 24 hours (or whatever is necessary) before writing the data so that the timestamps are in the past rather than the future.

Also you could try the influxdb batch node. Note that in that node you have to pass in the time in the timestamp property, not time.

Thanks @Colin, I tried the batch node and got the same results.

However, when I loaded the data from influxdb to grafana, it's strange but the timestamp was then correct :roll_eyes:

So, the timestamp that was injected into the influx node was;
ts

and viewing the same datapoint in Influxdb explorer shows a 4 minute difference;
influx explorer

and again, the same datapoint in Grafana;
grafana

So if it's the explorer that is wrong. Which version of influxdb are you using? I presume that explorer comes with it.

Influx version 2.61 on ubuntu, and yes, it's all part of the install.
I have already checked outstanding issues on git, and can't see anything.

It was a new experience using v2 Influx, as most of the setup & management is done in the UI, including creating buckets, data retention etc. It's much easier than v1!

I hope it can still be done via scripts. I use Ansible for configuring data retention and so on. Using a GUI for such things is ok for a one off, but if you need to be able to repeat the exercise it needs to be scriptable.

It does seem very odd the way the timestamps are off. In the explorer can you view the time in its raw form, as an integer?
It might be interesting to query the db in node red and see what you get.

A thought. Empty the measurement and check using the explorer that the data have gone. Then use the flow you posted to put it back again, and make sure that it reappears, and is still wrong.

Good idea @Colin - just tried that and the timestamps come back OK.

met

I tried that earlier, and yes the timestamps were still reported wrongly.
It appears to be a bug with influx explorer.

Is this it?

Confusing or what?

1 Like

Although I'm using oss and not their cloud service, it appears to be exactly the same issue, and one which they don't appear to want to fix, which seems strange....

I wasted hours yesterday, initially thinking that it was a problem with my formatting of the data or an issue with the node-red-contrib-influxdb node.
Oh well, for anyone else experiencing the same issue, this is a link to a workaround.

Thanks Colin

Do they yet have a cli version for interrogating the db like they did with 1.8, so you can just go in and ask to see the data on the command line?

Yes, it's added as a separate package - Install and use the influx CLI | InfluxDB OSS 2.7 Documentation although I don't use it, as I personally prefer to stick the the UI feature.

Which is fine, as long as it does what you expect :slight_smile:

The vendor does have a rather strange attitude to some bugs. Lots of people, including me, have reported that Telegraf has a very strange attitude to errors in the config. Instead of halting the input/output for the particular extension (e.g. MQTT output), it crashes the whole service so nothing works. Given that it isn't uncommon for one of the extensions to change a parameter in an update or make a change that prevents it from working, this makes little sense for something that is designed as a system monitoring tool. But they won't fix that either.

I must say that I am getting a it disillusioned with influxdb. I have been wondering about trying timescale.

Yes I noticed that too,. It seems ridiculous that small errors in the config can result in a total full stop.

Latest one wasn't even an error in the config, it was an error in the extension update. Completely stopped Telegraf after a minor update and wasted loads of time tracking down the problem.

I wish there were an alternative that was as easy to configure.

Hi @Paul-Reed
In Influx Data Explorer, assuming you have an aggregateWindow statement, can you remove it (or comment it out), reduce the time selector window to a small interval (1 hour or whatever) and then view the raw data to see it the timestamps agree with what you expect them to be? I seem to recall that using the aggregateWindow() function | Flux 0.x Documentation will make it appear that your timestamps are off.

EDIT: I just re-read the content above and it sounds like you already came to the same conclusion.

1 Like

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