Update last row of InfluxDB database

General
1

Hi everybody,

I have made flow where I store data into a InfluxDB database.

I have the following problem:
When the machine stops running, the downtime starts. I insert the start time to the database. (see picture)
When the downtime is finished (and the machine starts running) I want to add the finish timestamp to the last row. And I will also change the category from pending to "undefined". (so I can define it later)

I tried a lot but don't get the right result to add data to an existing row. :face_with_raised_eyebrow:
Is there anyone out here to help me?

This is the database:
database stops

This is the function:

function downtime
function downtime762×271 19.7 KB

Can I add a new line to the function node to update the database on the last row?

2

If you want to change an existing line you have to rewrite it, so find the line you want to overwrite (or keep the time value from it when you write it) and then you can write that record (with the timestamp you saved) and it will overwrite the previous record for that time.

3

Thinking about it I don't think you are doing it the optimum way anyway. Can you tell us what fields and tags you have in the records and what they mean?

4

Hi Colin, I a database with 3 columns.

  1. Start time
  2. Finish time
  3. Downtime category

As extra I'm overthinking if i want a 4th column with the total time (start time - finish time)

How do you think I can do it in the optimum way?

5

You mean a measurement with three columns. A measurement is the equivalent of a table in a conventional database.

Influxdb is a time series database, so every record should be a time and what happened at that time. Are you storing other stuff in influx as well? If not then it may be that you would be better using, for example, sqlite or postgresql or something similar.
If you do want to store this in influx then I suggest for the start and finish times have fields time, state and category, where state is running or stopped and category can be whatever is required. Then when the machine starts you write a record with state "running" and when it stops you write a record with state "stopped". It doesn't really fit well with the influx model though, I think if I were doing it I would use a different databsase for the part of the problem.

6

You gave me the insight to use a different database. That's a good idea I guess!

At the end I want every row in the database use as a stop category. So I think it is better to use a different database where I can do this in.

InfluxDB is a timeseries database. Does this also mean that instead of an id, a time value is taken as id?

Are there differents between sqlite or postgresql?
Which one do you prefer?

7

In influx, time is effectively the id, however you can have multiple records for the same time provided they have different tag combinations. To understand that sentence you would need to know about influx fields and tags and the differences between them.

Yes, massive. There is also mariadb and MySQL to consider.

It is not a matter of preference, it is a matter of choosing the best one for the application. Since I have no idea about yours it is difficult to say. For example whether you are going to have tens of milliions of records eventually, with a thousand users accessing it concurrently, or tens of records with only a few users at a time.

I think probably some googling would be a good idea. I expect you can find articles discussing the differences for different applications.

8

Thank you @Colin
Google will be my friend :slight_smile:

9

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