Time duration calculation between rows

acinvx · March 16, 2025, 8:38pm

I am using the last Grafana version with Infinity plug in.
It acquires data from an API that build a table in Grafana with timestamp and value information.
Changing the visualization to “state timeline” the chart shows in the tooltip the value of “duration” which calculates the values between two sequential timestamps (between rows).
I have been trying to calculate same value using “add field from calculation” without success since all the options just do operations between colums and not between rows.
I need to do this calculation in Grafana since API server application does not have resource to do it.
What would be the suggestions?
Would be possible to extract the duration values from “state timeline” chart?

Atttached the table in Grafana.

grant2 · March 17, 2025, 9:28am

Welcome @acinvx

Can you share your Infinity query (and ideally the URL that you are getting the data from) so that one can see what the data looks like in tabular form? I would think you can calculation the durations using the parser tools or UQL.

acinvx · March 17, 2025, 3:47pm

Hi @grant2. Thanks for your reply.
Transformation options would be best way since we are working in an initiative of “no code” integration between Grafana and vNode Gateway, a software that make available industrial realtime and historical data throught its API. Until now I have avoided to do queries using JSONata and UQL to make easier for common industrial users work with both softwares.

The software API endpoint is:
[localhost:3003/tag?cmd=history&path=/Random/Int1&start=2025-03-02T11:50:00.000&end=2025-03-02T11:51:00.000&options.mode=raw] and the JSON answer (example) is:

{
  "request": {
    "tag": "/Random/Int1",
    "start": 1740927000000,
    "end": 1740927060000,
    "options": {
      "remoteNode": null,
      "mode": "raw",
      "method": null,
      "interval": null,
      "deadband": null,
      "invalidAsNull": true,
      "limit": null
    }
  },
  "data": [
    [
      1740927000000,
      119,
      192,
      2
    ],
    [
      1740927002986,
      37,
      192
    ],
    [
      1740927008002,
      57,
      192
    ],
    [
      1740927013020,
      23,
      192
    ],
    [
      1740927018034,
      71,
      192
    ],
    [
      1740927023046,
      92,
      192
    ],
    [
      1740927028064,
      70,
      192
    ],
    [
      1740927033075,
      17,
      192
    ],
    [
      1740927038091,
      43,
      192
    ],
    [
      1740927043107,
      41,
      192
    ],
    [
      1740927048121,
      121,
      192
    ],
    [
      1740927053141,
      95,
      192
    ],
    [
      1740927058155,
      51,
      192
    ]
  ]
}

I am using Frontend parser, table format and selecting the columns through:

[0] as timestamp format as Time (UNIX ms)
[1] as value format as string

grant2 · March 17, 2025, 4:13pm

Hi @acinvx
I put your sample JSON into play.grafana.org here. Not sure what “Frontend parser” is. Can you complete the rest of your query parameters and save?

acinvx · March 17, 2025, 4:41pm

Hi @grant2.
I am having an issue to log in that I am trying to solve. For now I am sending a print screen of the configuration:

Frontend parser is the same of Default in the Parser setting.

Below the real data I am using in the project (same of previous one but instead of numerical values I have strings that describes production line status):

{
  "request": {
    "tag": "/SimuProducao/L1",
    "start": 1742136600000,
    "end": 1742158260000,
    "options": {
      "remoteNode": null,
      "mode": "raw",
      "method": null,
      "interval": null,
      "deadband": null,
      "invalidAsNull": true,
      "limit": null
    }
  },
  "data": [
    [
      1742136600000,
      "Parada Programada - CIP (Lavagem)",
      192,
      2
    ],
    [
      1742150727131,
      "Em Operação - Envase",
      192
    ],
    [
      1742151001069,
      "Em Operação - Preparação de Matéria-Prima",
      192
    ],
    [
      1742151301015,
      "Em Operação - Filtragem",
      192
    ],
    [
      1742151601023,
      "Em Operação - Rotulagem",
      192
    ],
    [
      1742151901021,
      "Em Operação - Preparação de Matéria-Prima",
      192
    ],
    [
      1742152201011,
      "Em Operação - Pasteurização",
      192
    ],
    [
      1742152501025,
      "Em Operação - Rotulagem",
      192
    ],
    [
      1742152801009,
      "Parada Programada - Manutenção Preventiva",
      192
    ],
    [
      1742153101009,
      "Em Operação - Pasteurização",
      192
    ],
    [
      1742153401011,
      "Em Operação - Embalagem",
      192
    ],
    [
      1742153701011,
      "Parada Programada - Manutenção Preventiva",
      192
    ],
    [
      1742154001017,
      "Em Operação - Envase",
      192
    ],
    [
      1742154301023,
      "Parada Não Planejada - Ajuste de Máquina",
      192
    ],
    [
      1742154601019,
      "Em Operação - Filtragem",
      192
    ],
    [
      1742154901027,
      "Em Operação - Envase",
      192
    ],
    [
      1742155201020,
      "Em Operação - Preparação de Matéria-Prima",
      192
    ],
    [
      1742155501010,
      "Em Operação - Filtragem",
      192
    ],
    [
      1742155801023,
      "Em Operação - Rotulagem",
      192
    ],
    [
      1742156101011,
      "Em Operação - Preparação de Matéria-Prima",
      192
    ],
    [
      1742156401010,
      "Em Operação - Pasteurização",
      192
    ],
    [
      1742156701023,
      "Em Operação - Rotulagem",
      192
    ],
    [
      1742157001024,
      "Em Operação - Extração do Suco",
      192
    ],
    [
      1742157301012,
      "Em Operação - Pasteurização",
      192
    ],
    [
      1742157601025,
      "Em Operação - Embalagem",
      192
    ]
  ]
}

yosiasz · March 17, 2025, 5:15pm

so in this data that you have provided which rows are you trying to base your calculation on?
1&2, 3&4. etc? What if there is odd number of rows what would 5 before compared to if there is no row 6?

Using the json you posted please demonstrate the result you expect as json.
if you dont want to use UQL and jsonata then you will most probably not be able to solve it.

grant2 · March 17, 2025, 5:22pm

Here is the data in Play:

yosiasz · March 17, 2025, 5:37pm

so for example for Em Operação - Pasteurização there are 4 rows. how will that be calculated, which values will be used to calculate duration?

acinvx · March 17, 2025, 6:27pm

@grant2

Timestamp values are in a sequence and each value represents the “start” of production status. What I need to calculate is the difference between each sequential rows (for example: timestamp/row2 - timestamp/row1 = duration line2; timestamp/row3 - timestamp/row2 = duration line3). The end target is to calculate individual and total operation and stoppage times to use in KPIs calculations (MTBF, MTTR, OEE, etc.).

Just as a note, “state timeline” chart does exactly what I need when make available the “duration” information. The problem is that information is not available to use.

I saw that you put my data in the https://play.grafana.org/ but did not configure the columns with [0] and [1] in order to have filtered table.

Hope this information clarify a little bit more the challenge.
Thank you!

@yosiasz I think this answer also respond your question. See below the “state timeline” example with the data provided:

yosiasz · March 17, 2025, 6:33pm

Please provide the above as it is a bit more confusing to be honest

grant2 · March 17, 2025, 6:39pm

Done. I will have a look later when I have more time, but as @yosiasz stated, UQL and jsonata may be needed.

acinvx · March 17, 2025, 6:40pm

Sorry if my english is not too clear.
I already have the data organized from a query in the API and in this way I do not need to query the data from the API source, just do the calculation throught transformations based in the table that I am including here (same from first post). the JSON data is the same I posted in the previous replies:

yosiasz · March 17, 2025, 6:45pm

What will the desired final data look like

Como serão os dados finais desejados?

acinvx · March 17, 2025, 6:48pm

What I need is an additional column in the table with duration time [What I need to calculate is the difference between each sequential rows (for example: timestamp/row2 - timestamp/row1 = duration line2; timestamp/row3 - timestamp/row2 = duration line3).]

yosiasz · March 17, 2025, 6:49pm

we get that now provide another sample data as json or as csv of what the final data will look like when the calculation is done.

entendemos isso agora forneça outros dados de amostra como json ou como csv de como os dados finais ficarão quando o cálculo for feito.

acinvx · March 17, 2025, 6:57pm

Here is:

[
{
“timestamp”: 1742151144229,
“value”: “Em Operação - Preparação de Matéria-Prima”,
“duration”: 156786
},
{
“timestamp”: 1742151301015,
“value”: “Em Operação - Filtragem”,
“duration”: 300008
},
{
“timestamp”: 1742151601023,
“value”: “Em Operação - Rotulagem”,
“duration”: 299998
},
{
“timestamp”: 1742151901021,
“value”: “Em Operação - Preparação de Matéria-Prima”,
“duration”: 299990
},
{
“timestamp”: 1742152201011,
“value”: “Em Operação - Pasteurização”,
“duration”: 300014
},
{
“timestamp”: 1742152501025,
“value”: “Em Operação - Rotulagem”,
“duration”: 299984
},
{
“timestamp”: 1742152801009,
“value”: “Parada Programada - Manutenção Preventiva”,
“duration”: 300000
},
{
“timestamp”: 1742153101009,
“value”: “Em Operação - Pasteurização”,
“duration”: 300002
},
{
“timestamp”: 1742153401011,
“value”: “Em Operação - Embalagem”,
“duration”: 300000
},
{
“timestamp”: 1742153701011,
“value”: “Parada Programada - Manutenção Preventiva”,
“duration”: 300006
},
{
“timestamp”: 1742154001017,
“value”: “Em Operação - Envase”,
“duration”: 300006
},
{
“timestamp”: 1742154301023,
“value”: “Parada Não Planejada - Ajuste de Máquina”,
“duration”: 299996
},
{
“timestamp”: 1742154601019,
“value”: “Em Operação - Filtragem”,
“duration”: null
}
]

acinvx · March 18, 2025, 12:38am

@yosiasz and @grant2,

I will continue studying the alternatives for a “no code” approach but for now I decided to do it with JSONata and already have the data organized in a way that I can do the calculations needed.

Thank you very much for your support looking this subject.

Here is the JSONata expression I used as information for people interested how I solved this part of the project:

$map($$.data, function($v, $i, $a) {
($i < $count($a) - 1) ? {
“Status”: $v[1],
“Start”: $v[0],
“Stop”: $a[$i+1][0],
“Duration”: $a[$i+1][0] - $v[0]
} : null

This will create 4 columns with status, start, stop and duration and put the timestamps received in a sequence as start and stop values. Also, the stop value is the start value for each new row.

grant2 · March 18, 2025, 1:20am

Nice work @acinvx

I put your JSONata expression into the dashboard and still am not getting it. Do I have the syntax correct? Maybe another “)”?

yosiasz · March 18, 2025, 1:27am

this might help to get the juices flowing.

(
$.data.{
    'metric' : $fromMillis($[0]),
    'time': $[1],
    'value': $[2]
};
)

OP is not very thorough in his posts. each post is more confusing than the previous.

acinvx · March 18, 2025, 3:37pm

@yosiasz I do not want to disrespect your experience and knowledge as Grafana champion but I think that your comments are not correct. Everything is very clear in my answers if you read them.
In my last comment I informed the solution I found using JSONata and that worked for this moment.
So, there is nothing for your different comments about confusion.
If you have specific questions please let me know and I will be happy to clarify.

Topic		Replies	Views
How to Calculate Difference Between Consecutive Data Points Using Infinity Plugin? Grafana plugins , transformations , infinity-datasource	6	209	December 8, 2024
Using Grafana for analysing data and calculations Grafana grafana	2	127	June 25, 2024
Create a State Timeline Chart Grafana query-help , bar-chart , datasource , time-series	9	2108	June 17, 2024
Help with FLUX query: Duration between "Now/d" and "Now" timestamps InfluxDB query-help , flux	13	180	December 13, 2024
Infinity plugin EPOCH time Dashboards plugins , query-help , infinity-datasource	3	1495	December 13, 2023

Time duration calculation between rows

Related topics