How to get total duration of state?


I am trying to calculate total duration of up cycle of the press machine we have.
Here is what the logic is:

  • Press is consider up when the pressure is above 80
  • Else it needs to be considered as down
  • I want to get total duration of the up cycles that is happening

The query that I am using looks like this:

from(bucket: "ProdOpcDaBucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "P2500_Tag8")
  |> aggregateWindow(every: v.windowPeriod, fn: (column, tables=<-) => tables 
    |> quantile(q: 0.99), createEmpty: false)
  |> map(fn: (r) => ({ r with _value: (if r["_value"] > 80 then 1 else 0) }))
  |> stateDuration(fn: (r) => r._value == 1, column: "_upDuration", unit: 1s)
  |> stateDuration(fn: (r) => r._value == 0, column: "_downDuration", unit: 1s)
  |> map(fn: (r) => ({ 
        r with _duration: (
            if r["_value"] == 0 
            then r._downDuration 
            else r._upDuration
  |> drop(columns: ["_start", "_stop", "_field", "_upDuration", "_downDuration"])
  |> yield(name: "Extrusion-Cycle")

The result set of query looks like this:

As you can see stateDuration gives the all the records which was part of cycle.
What I want is to fetch only last record of the given up or down cycle highlighted in red circle.

I also tried to use monitor.statechangesOnly() but that gives me first record of cycle (highlighted in blue) instead of last

Could someone please point me in the right direction here ?

If you’re visualizing the data with Grafana then you can use the Reduce transformation and calculate as “Last”, this should show the last state recorded in your _duration column. The Stat panel would be best for this being a single value.

Hi @R0bR ,

Thanks for responding to this. I donot want to reduce it to single record but actually want to group the records by state cycles so that I can find duration of each cycle that is happening. Also I want to plot it on timeseries chart so that all of the Long cycles that happened is clearly visible to operator

I’m not sure how you can get that plotted to a time series but if you are not capturing all the points in the time series graph it’s going to be a pretty bare graph with just the point prior to a state change plotted. It doesn’t seem like a good representation of the data. You’d be better off using the State Timeline graph in Grafana to plot a continuous graph and changing color for each state change, this would show you the duration on mouse over.

I generally don’t over do my flux queries, I get it the data filtered to what I need and use Grafana to transform it to what I need in the dashboards. You could mess with the Grafana transformations like “Group” to try to achieve what you are looking for.

@harshdesai991 I agree with @R0bR that the desired data structure doesn’t make for great time series visualization. Is the end goal of the query to just visualize state cycles over time? Or is it to know how much time is spent in each state?

If it’s just the visualization you’re after and you want to do it in a InfluxDB dashboard, you can use the following query and the InfluxDB mosaic visualization.

from(bucket: "ProdOpcDaBucket")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r["_measurement"] == "P2500_Tag8")
    |> aggregateWindow(
        every: v.windowPeriod,
        fn: (column, tables=<-) => tables
            |> quantile(q: 0.99),
        createEmpty: false,
    |> map(fn: (r) => ({r with state: if r["_value"] > 80 then "up" else "down"}))