How to handle discrete data in Influxdb and Flux query

Fluxlang
, ,
1

Hii, I am very new (can say noob) in Influxdb and considering influx database for my next project. I’m working on a factory project where I’m getting data from PLC and data is in narrow format (get data only when data change and function previous fill will work fine for this task), now i getting data in format like

timestamp            | machine_state | state code | good count | reject count |watch dog
2020-12-21T03:07:52Z | fault         | 3          | 500        | 3            | true
2020-12-21T03:08:48Z | null          | null       | null       | null         | false
2020-12-21T03:09:32Z | idle          | 1          | null       | null         | true
2020-12-21T03:10:11Z | executing     | 1          | 510        | 4            | false
2020-12-21T03:11:52Z | null          | null       | 520        | null         | true
2020-12-21T03:15:22Z | fault         | 5          | 540        | null         | false
2020-12-21T03:20:52Z | null          | null       | null       | null         | true

i’m expecting following out put from this data (assuming current time is 2020-12-21T03:25:52Z )

event history = [
{
machine_state:“fault”,
from:2020-12-21T03:07:52Z,
to:2020-12-21T03:09:48Z,
duration:3 min.
state_code:3
},
{
machine_state:“idle”,
from:2020-12-21T03:09:48Z,
to:2020-12-21T03:10:32Z,
duration:1 min.
state_code:1
},
{
machine_state:“executing”,
from:2020-12-21T03:10:32Z,
to:2020-12-21T03:15:22Z,
duration:5 min.
state_code:1
},
{
machine_state:“fault”,
from:2020-12-21T03:15:22Z,
to:2020-12-21T03:25:52Z (current time),
duration:10 min.
state_code:5
}
],

Thanks in advance

2

@zenovix How are you storing the data in InfluxDB? In your sample data, which columns are fields and which are tags?

3

Thanks @scott for your consideration, yes all these data are field and tag is machine name which is another column

4

I think this thread is similar to the problem you’re trying to solve and may provide. One thing to note however is that Flux currently only returns annotated CSV. It doesn’t return JSON (yet).

1 Like
5

Thank you @scott for your valuable suggestion, i need your assistance that can i achieve same result in json using InfluxQL