Manually data insert


#1

Hello there,
I’m pretty new with InfluxDB, and I am sorry to say that I am experiencing some troubles trying to understand how it works (as I work everyday with Oracle and SQL).
So I’m using InfluxDB for storing data from HomeAssistant sensors (and showing them with Grafana)
Everything works quite well except for some smart plugs with energy monitoring.
Energy isn’t captured by InfluxDB (maybe it’s not event exposed by the sensor) but, as I can read it from the original smart plug app (i.e. Ewelink, or MiHome, or Tuya) I would like to manually insert it in InfluxDB, or better: I would like to insert daily kwh consumed.
So it should be something like:

INSERT kwh, entity_id=plug1 value=10
INSERT kwh, entity_id=plug2 values 5

but I am missing how to insert a date, so the value 10 for plug1 is for example for the 1st of november 2018

Can someone help me?
And moreover, I’ve not installed the command line tool, is it possibile to make insert from the web interface?

Thansk in advance!


#2

Hi @CptCatarro, Welcome and thanks for checking out the project!

The INSERT syntax is only supported in the influx CLI tool. Everything after the INSERT, e.g. from your example kwh,entity_id=plug1 value=10 is InfluxDB line protocol. Line protocol has an optional way to specify a timestamp by including a Unix timestamp at nanosecond precision at the end of the line. (If no timestamp is provided, InfluxDB will set a timestamp automatically based on the time it received the point.) Below is an example of a line protocol point with the timestamp.

kwh,entity_id=plug1 value=10 1257894000000000000

If you do not want to use the influx CLI and it’s INSERT syntax, you can write directly to the InfluxDB HTTP API. Check out the docs on the /write endpoint, which has examples. The influx CLI program itself actually uses the HTTP API to send points to InfluxDB.

You might also find the /write endpoint’s precision URL parameter for setting the precision of the timestamp on line protocol point useful. This allows a Unix timestamp to be sent with second level precision instead of nanosecond level precision.

Writes can be made through Chronograf by going to the “Data explorer” page and clicking the “Write data” button on the top right-hand side.


#3

Hi Gunnar, thanks a lot for your answer.
Let’s see if I’ve understood…

Using CLI i can simply write
INSERT kwh, entity_id=plug1 value=10 1541930239 (current timestamp)

In this case, how do I set precision? I still haven’t decided if I want to have simply a day (i.e. 11/11/2018) or the end of the day (i.e. 11/11/2018 23.59.59)

Using HTTP API (better I think) I should write something like

curl -i -XPOST “http://localhost:8086/write?db=mydb&precision=s” --data-binary ‘kwh,entity_id=plug1 value=10 1541980799’

And it should be converted to 11/11/2018 23.59.59

Is this correct?

Now… I’ve also seen that it is possible to use a text file to write multiple points with something like
curl -i -XPOST ‘http://localhost:8086/write?db=mydb’ --data-binary @cpu_data.txt

Where should I put the TXT file to be seen from influxdb?

Thanks a lot again, I like this influxdb a lot :slight_smile:


#4

In this case, how do I set precision?

The precision can be set by typing in precision x as a CLI command before sending the INSERT command. The valid values for precision are rfc3339, h, m, s, ms, u or ns. Where RFC3339 is the human readable timestamp format, e.g. 11-11-2018T23.59.59.000Z. Note that the RFC3339 precision option is a convenience feature of the CLI and is not in the HTTP API, which always expects a Unix timestamp format.

Using HTTP API (better I think) I should write something like… And it should be converted to 11/11/2018 23.59.59

The influx CLI is uses the InfluxDB’s HTTP API underneath, so you are removing a “layer” when interacting directly with the HTTP API, otherwise it’s essentially the same interface. The CLI and HTTP API’s precision settings simply tell InfluxDB what format the timestamp is in on ingest. When querying, InfluxDB will return the Unix timestamp converted to RFC3339 format by default unless the epoch query string parameter is set. The precision on write has no impact on the timestamp format returned in queries.

Where should I put the TXT file to be seen from influxdb?

In your curl command, the cpu_data.txt file should be a file in the current directory where the curl command is executed, i.e. the @ points to a relative path on disk from the current working directory. Once the curl command is executed and the file’s line protocol contents are successfully written to InfluxDB, the data is in the database and can be queried from the database. The file itself is not used and is redundant.