Working with Influxdb 2.x timestamp

General
1

I pull inverter data from the manufacturer's cloud service and would like to store it in a local Influxdb instance. The data pulled down is the data for one day and comprises up to 280 datapoints. This is delivered as a single payload of nested arrays. I use the split node to create a single payload for each array (example below). The issue I have is that the timestamp (eg 1658016266) isn't being recognised as such. The value is valid, having dropped it into Epoch Converter, it handles it as I would expect. I've extracted the timestamp from the payload (from where it wasn't recognised as such by Influxdb) and created a msg.timestamp key with the value transferred. Influxdb still generates its own internal timestamp for the measurement whether there is a timestamp key in the payload or a separate msg.timestamp with the message. msg.measurement is being handled as I would expect but not the timestamp. Any pointers as to what I've missed?

msg.payload is

{

"status":"NORMAL",

“vpv1":35.9,

“vpv2":30.8,

… (additional key values deleted for brevity)

“fac":50.05,

“loadPower":330,

“temperature":39.2,

“import":0,

“export":23

}
2

Show us an example of the payload you are sending to influx, with the timestamp as a number, and what error you are getting from influx please.

3

Thanks for responding. This is one of the payloads, copied from the debug node (with minor redactions):

{“measurement":"SDxxxxxx","day":"2022-07-17","headers":{“date":"Sat, 06 Aug 2022 07:39:23 GMT","content-type":"application/json","transfer-encoding":"chunked","connection":"close","server":"nginx/1.20.0","cache-control":"no-cache, private","internal-api-version":"1","x-node-red-request-node":"37286ff4"},"method":"GET","url":"https://www.givenergy.cloud/internal-api/inverter/data/SDxxxxxx/2022-07-17","payload":{“status":"NORMAL","vpv1":30.2,"vpv2":29.5,"ipv1":0,"ipv2":0,"ppv1":0,"ppv2":0,"ppv":0,"pac":19,"vacr":249.4,"iacr":1,"fac":49.93,"loadPower":0,"meter1ActivePower":19,"peps":0,"invVoltage":247.9,"invFrequency":49.93,"temperature":39.8,"batVoltage":52.76,"batPower":6,"batTemperature":25,"batPercent":90,"pvEnergyToday":0,"pvEnergyTotal":2984.7,"consumptionEnergyToday":0,"consumptionEnergyTotal":602.2,"exportedEnergyToday":0,"gridExportEnergyTotal":2556.2,"etoday":0,"etotal":2913.2,"invImportEnergyToday":0,"invImportEnergyTotal":17.4,"importedEnergyToday":0,"gridImportEnergyTotal":262.6,"chargeEnergyToday":0,"charge_energy_total":683.55,"dischargeEnergyToday":0.2,"discharge_energy_total":683.55,"import":0,"export":19},"statusCode":200,"responseUrl":"https://www.givenergy.cloud/internal-api/inverter/data/SDxxxxxx/2022-07-17","redirectList":[],"retry":0,"responseCookies":{“laravel_token":{“expires":"Sat, 20-Aug-2022 07:39:22 GMT","Max-Age":"1209600","path":"/","samesite":"lax","value":"xxxREDACTEDxxx"},"XSRF-TOKEN":{“expires":"Sat, 20-Aug-2022 07:39:22 GMT","Max-Age":"1209600","path":"/","samesite":"lax","value":" xxxREDACTEDxxx "},"laravel_session":{“expires":"Sat, 20-Aug-2022 07:39:22 GMT","Max-Age":"1209600","path":"/","samesite":"lax","value":" xxxREDACTEDxxx "}},"parts":{“id":"4dde13800eefc8a2","type":"array","count":285,"len":1,"index":282},"_msgid":"c5bd6ae016cf8270","time":1658016868}

In the original payload, the timestamp for each individual data point appears as a key value "timestamp" towards the end of the object. That was loaded into Influxdb as a key value pair but wasn't used by Influxdb as the timestamp. After the split node to break up the datapoints for each day into individual payloads, I removed the key value pair from the object and added the timestamp to the message as msg.time. As mentioned earlier msg.measurement is being handled as expected but not the timestamp. When I query the Influxdb bucket, the timestamp date range for all the data points which have loaded are for the current day and are the time the data was sent from Node-RED. Any pointers appreciated.

Admin edit: added newlines before and after backticks to format data correctly.

4

Which influx node are you using, and looking at the help text for the node, which particular data format are you trying to achieve? Note that, in influxdb, field and tag values can only be simple data values, not objects.

When pasting data and code use the </> button at the top of the forum entry window and it will format it nicely.

5

In fact, can you get the value in the debug output again, hover over the top level object and two little buttons will appear at the right hand side. Hover over those and click the Copy Value button and paste that here using the </> button. Then it will be more understandable.

6

I'll try again with the formatting (I manually typed three backticks before and after but they're obviously different backticks from the one in the menu bar!). Thanks.

Node is influxdb out. In the debug node, the timestamp value appears in blue and clicking on it toggles between hex, decimal, zulu and local timezone display. Copying the value to Epoch Converter does convert correctly, so I think I have a value which, at least Node-RED recognises as a timestamp.

{“measurement":"SDxxxxxx","day":"2022-07-17","headers":{“date":"Sat, 06 Aug 2022 08:12:14 GMT","content-type":"application/json","transfer-encoding":"chunked","connection":"close","server":"nginx/1.20.0","cache-control":"no-cache, private","internal-api-version":"1","x-node-red-request-node":"debca0d8"},"method":"GET","url":"https://www.givenergy.cloud/internal-api/inverter/data/SDxxxxxx/2022-07-17","payload":{“status":"NORMAL","vpv1":30.2,"vpv2":29.5,"ipv1":0,"ipv2":0,"ppv1":0,"ppv2":0,"ppv":0,"pac":19,"vacr":249.4,"iacr":1,"fac":49.93,"loadPower":0,"meter1ActivePower":19,"peps":0,"invVoltage":247.9,"invFrequency":49.93,"temperature":39.8,"batVoltage":52.76,"batPower":6,"batTemperature":25,"batPercent":90,"pvEnergyToday":0,"pvEnergyTotal":2984.7,"consumptionEnergyToday":0,"consumptionEnergyTotal":602.2,"exportedEnergyToday":0,"gridExportEnergyTotal":2556.2,"etoday":0,"etotal":2913.2,"invImportEnergyToday":0,"invImportEnergyTotal":17.4,"importedEnergyToday":0,"gridImportEnergyTotal":262.6,"chargeEnergyToday":0,"charge_energy_total":683.55,"dischargeEnergyToday":0.2,"discharge_energy_total":683.55,"import":0,"export":19},"statusCode":200,"responseUrl":"https://www.givenergy.cloud/internal-api/inverter/data/SDxxxxxx/2022-07-17","redirectList":[],"retry":0,"responseCookies":{“laravel_token":{“expires":"Sat, 20-Aug-2022 08:12:13 GMT","Max-Age":"1209600","path":"/","samesite":"lax","value":"xxxREDACTEDxxx"},"XSRF-TOKEN":{“expires":"Sat, 20-Aug-2022 08:12:13 GMT","Max-Age":"1209600","path":"/","samesite":"lax","value":" xxxREDACTEDxxx "},"laravel_session":{“expires":"Sat, 20-Aug-2022 08:12:13 GMT","Max-Age":"1209600","path":"/","samesite":"lax","value":" xxxREDACTEDxxx "}},"parts":{“id":"9e015a8283fd972a","type":"array","count":285,"len":1,"index":282},"_msgid":"f33315af9c6b56e7","time":1658016868}
7

It was just that you did not have new lines after and before the backticks.

Did you see my previous post about getting it from the debug node using the copy button?

Also you have not said which format you trying to achieve. With the Influx Out node there are a number of options. If you are providing a time with that node then it should be with the other fields.

8

Also it is probably easier just to show us msg.payload as that is all that is relevant except for msg.measurement.

9

New lines. Ok Thanks.
Debug node payload is:

{"status":"NORMAL","vpv1":30.2,"vpv2":29.5,"ipv1":0,"ipv2":0,"ppv1":0,"ppv2":0,"ppv":0,"pac":19,"vacr":249.4,"iacr":1,"fac":49.93,"loadPower":0,"meter1ActivePower":19,"peps":0,"invVoltage":247.9,"invFrequency":49.93,"temperature":39.8,"batVoltage":52.76,"batPower":6,"batTemperature":25,"batPercent":90,"pvEnergyToday":0,"pvEnergyTotal":2984.7,"consumptionEnergyToday":0,"consumptionEnergyTotal":602.2,"exportedEnergyToday":0,"gridExportEnergyTotal":2556.2,"etoday":0,"etotal":2913.2,"invImportEnergyToday":0,"invImportEnergyTotal":17.4,"importedEnergyToday":0,"gridImportEnergyTotal":262.6,"chargeEnergyToday":0,"charge_energy_total":683.55,"dischargeEnergyToday":0.2,"discharge_energy_total":683.55,"import":0,"export":19}

I'm assuming (dangerous I know) that Influxdb will recognise a timestamp. I have another flow which uses Influxdb 1.8. The data for that instance is a much simpler object with two key values, the second of which is the time in the same format as this. In the payload received from the cloud with 280+ datapoints, the timestamp has three other key values after it. I wonder if making it the last key value would enable Influxdb 2.x to handle it correctly. If that's the case, how do I add a key value to the payload at the end?

10

I don't see the time field in there.

11

Yes. I mentioned int he first post that I moved the timestamp to msg.time hoping that it would be handled as msg.measurement is. Since my previous post, I have now moved the timestamp to be a key value as the last element at the end of the object.

{"status":"NORMAL","vpv1":30.2,"vpv2":29.5,"ipv1":0,"ipv2":0,"ppv1":0,"ppv2":0,"ppv":0,"pac":19,"vacr":249.4,"iacr":1,"fac":49.93,"loadPower":0,"meter1ActivePower":19,"peps":0,"invVoltage":247.9,"invFrequency":49.93,"temperature":39.8,"batVoltage":52.76,"batPower":6,"batTemperature":25,"batPercent":90,"pvEnergyToday":0,"pvEnergyTotal":2984.7,"consumptionEnergyToday":0,"consumptionEnergyTotal":602.2,"exportedEnergyToday":0,"gridExportEnergyTotal":2556.2,"etoday":0,"etotal":2913.2,"invImportEnergyToday":0,"invImportEnergyTotal":17.4,"importedEnergyToday":0,"gridImportEnergyTotal":262.6,"chargeEnergyToday":0,"charge_energy_total":683.55,"dischargeEnergyToday":0.2,"discharge_energy_total":683.55,"import":0,"export":19,"time":1658016868}
12

Is it working? If not then what error?
Did you remember to set the time precision in the node to seconds?

13

Colin, Thanks for your help but the issue is probably Influxdb rather than Node-RED. I had been seeing data points in Influxdb, but could not see any data point timestamps. Now that I've added the timestamp at the end of the object, the same Influxdb out node no longer seems to be able to post the data to the database, or at least I'm unable to find any data points. Hmm it would be helpful if I could post this question to an Influxdb forum.

14

Have you dropped the measurement, added a few points, and then examined the contents using the influx command line to see what is in the database?
Select * from the_measurement
Or something like that.

If you don't provide a time when as a point it will add the data using the current time.

Unlikely.
It isn't an influx question, it is a matter of getting node red to send it the right data.

15

Oh, it is influx 2, so you have to use the flux language to query it. Same principle though.

16

Colin, yes it's Influx 2.3. I decided to migrate to it from 1.8 by setting up a new project, leaving my existing projects' data being sent to the old instance. When I've got my head round the new way of doing things, I'll modify the existing nodes sending to 1.8 as appropriate. I have posted onto the Influx community but there's only one other person who seems to be using Node-RED and they're trying to get the data in by Telegraf and they are also struggling. I suspect that my issue is that Influx have changed the way it accepts the data and the node hasn't kept up with the changes. Ironically, I chose to start using Influxdb 2.3 because it has a graphical UI which I thought would make life easier but the change means I can't simply drop a measurement any longer. I've got a few thousand data points currently held by my inverter manufacturer in their cloud service but I'd like to pull the data together with sun position and other data, so my own system is a way to enable me to pull down data every day using Node-RED and do the Grafana visualisation (unless the Influx UI can also do it) at my leisure.

I've checked the issues log on Github for the contrib and there are some suggestions for improvement there but the original developer seems to have either lost interest or doesn't currently have the time.

I may have to look at using the REST API rather than the node, which is a pity because it is a very neat package.

17

Do you mean you have been doing all this, outputting to your production system and potentially messing up your stored data?

Temporarily change your flow to output to a test measurement till you get it working. Add a few samples then inspect the database and see what is there.
Did you answer the question on how you have set the time precision?

[Edit] Fixed google induced error in first sentence so it now makes sense.

18

@Bodad I have done something similar a few weeks ago. Did you try my flow? Using Node-RED to transfer data from InfluxDB 1.7 to 2.3, but receiving ENOBUFS error when dataset is too large - #9 by grant1

19

Colin, no. I have built a new Raspberry Pi as a development platform with a dev instance of Node-RED and the Influxdb 2.3 instance. I'm pulling data down from the cloud into dev flows and pushing the data to the new database bucket. The production platform of Node-RED and Influxdb 1.8 are still working and haven't been touched. Once I have the issues sorted, I will look to migrate the existing flows onto the dev system for testing and then I'll make the dev system into the production system. Once that's been working for a while, I'll trash the current production system and rebuild again with a fresh install.

20

OK, so do what I suggested, drop the measurement, or use a new test one, add a few samples then inspect the database and see what has been added.