# Time duration calculation between rows

**URL:** <https://community.grafana.com/t/time-duration-calculation-between-rows/146450>\
**Category:** Dashboards\
**Tags:** infinity-datasource\
**Created:** [March 16, 2025, 8:38pm UTC](https://community.grafana.com/t/time-duration-calculation-between-rows/146450 "2025-03-16T20:38:25Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![acinvx](https://avatars.discourse-cdn.com/v4/letter/a/8baadc/32.png) [@acinvx](https://community.grafana.com/u/acinvx)\
**Post date:** [March 16, 2025, 8:38pm UTC](https://community.grafana.com/t/time-duration-calculation-between-rows/146450/1 "2025-03-16T20:38:25Z")

</div>

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.

 ![grafana1](https://us1.discourse-cdn.com/grafana/original/3X/5/7/57fd4b223d751ce2fe0733b789deb08e163ed872.png)

---

<div class="post-metadata">

**Author:** ![grant2](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/grant2/32/42281_2.png) [@grant2](https://community.grafana.com/u/grant2)\
**Post date:** [March 17, 2025, 9:28am UTC](https://community.grafana.com/t/time-duration-calculation-between-rows/146450/2 "2025-03-17T09:28:22Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![acinvx](https://avatars.discourse-cdn.com/v4/letter/a/8baadc/32.png) [@acinvx](https://community.grafana.com/u/acinvx)\
**Post date:** [March 17, 2025, 3:47pm UTC](https://community.grafana.com/t/time-duration-calculation-between-rows/146450/3 "2025-03-17T15:47:50Z")

</div>

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:

```auto
{
  "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

---

<div class="post-metadata">

**Author:** ![grant2](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/grant2/32/42281_2.png) [@grant2](https://community.grafana.com/u/grant2)\
**Post date:** [March 17, 2025, 4:13pm UTC](https://community.grafana.com/t/time-duration-calculation-between-rows/146450/4 "2025-03-17T16:13:15Z")

</div>

Hi @acinvx  
I put your sample JSON into [play.grafana.org](http://play.grafana.org) [here](https://play.grafana.org/d/beg4kyweviu4ge/acinvx?orgId=1&from=now-6h&to=now&timezone=utc). Not sure what “Frontend parser” is. Can you complete the rest of your query parameters and save?

---

<div class="post-metadata">

**Author:** ![acinvx](https://avatars.discourse-cdn.com/v4/letter/a/8baadc/32.png) [@acinvx](https://community.grafana.com/u/acinvx)\
**Post date:** [March 17, 2025, 4:41pm UTC](https://community.grafana.com/t/time-duration-calculation-between-rows/146450/5 "2025-03-17T16:41:36Z")

</div>

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:

 ![Captura de tela 2025-03-17 132807](https://us1.discourse-cdn.com/grafana/original/3X/4/6/4626bf12de911d0d943cdd2c92b3a3ccdfc331ff.png)

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):

```auto
{
  "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
    ]
  ]
}

```

---

<div class="post-metadata">

**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\
**Post date:** [March 17, 2025, 5:15pm UTC](https://community.grafana.com/t/time-duration-calculation-between-rows/146450/6 "2025-03-17T17:15:10Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![grant2](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/grant2/32/42281_2.png) [@grant2](https://community.grafana.com/u/grant2)\
**Post date:** [March 17, 2025, 5:22pm UTC](https://community.grafana.com/t/time-duration-calculation-between-rows/146450/7 "2025-03-17T17:22:04Z")

</div>

Here is the data in Play:

> **[Grafana](https://play.grafana.org/d/beg4kyweviu4ge/acinvx?orgId=1&from=2025-03-17T11:19:13.885Z&to=2025-03-17T17:19:13.885Z&timezone=utc)**

---

<div class="post-metadata">

**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\
**Post date:** [March 17, 2025, 5:37pm UTC](https://community.grafana.com/t/time-duration-calculation-between-rows/146450/8 "2025-03-17T17:37:38Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![acinvx](https://avatars.discourse-cdn.com/v4/letter/a/8baadc/32.png) [@acinvx](https://community.grafana.com/u/acinvx)\
**Post date:** [March 17, 2025, 6:27pm UTC](https://community.grafana.com/t/time-duration-calculation-between-rows/146450/9 "2025-03-17T18:27:31Z")

</div>

@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/](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:

 ![statetimeline](https://us1.discourse-cdn.com/grafana/original/3X/e/f/ef8fb89e918852821613edd907eb1c825e5ef019.png)

---

<div class="post-metadata">

**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\
**Post date:** [March 17, 2025, 6:33pm UTC](https://community.grafana.com/t/time-duration-calculation-between-rows/146450/10 "2025-03-17T18:33:39Z")

</div>

> [@yosiasz](#):
>
> Using the json you posted please demonstrate the result you expect as json.

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

---

<div class="post-metadata">

**Author:** ![grant2](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/grant2/32/42281_2.png) [@grant2](https://community.grafana.com/u/grant2)\
**Post date:** [March 17, 2025, 6:39pm UTC](https://community.grafana.com/t/time-duration-calculation-between-rows/146450/11 "2025-03-17T18:39:25Z")

</div>

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

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

 ![image](https://us1.discourse-cdn.com/grafana/original/3X/4/9/493d78a8c83d204a6d5b8ae363db361362441f6c.png)

---

<div class="post-metadata">

**Author:** ![acinvx](https://avatars.discourse-cdn.com/v4/letter/a/8baadc/32.png) [@acinvx](https://community.grafana.com/u/acinvx)\
**Post date:** [March 17, 2025, 6:40pm UTC](https://community.grafana.com/t/time-duration-calculation-between-rows/146450/12 "2025-03-17T18:40:12Z")

</div>

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:

 ![grafana1](https://us1.discourse-cdn.com/grafana/original/3X/5/7/57fd4b223d751ce2fe0733b789deb08e163ed872.png)

---

<div class="post-metadata">

**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\
**Post date:** [March 17, 2025, 6:45pm UTC](https://community.grafana.com/t/time-duration-calculation-between-rows/146450/13 "2025-03-17T18:45:52Z")

</div>

What will the desired final data look like

Como serão os dados finais desejados?

---

<div class="post-metadata">

**Author:** ![acinvx](https://avatars.discourse-cdn.com/v4/letter/a/8baadc/32.png) [@acinvx](https://community.grafana.com/u/acinvx)\
**Post date:** [March 17, 2025, 6:48pm UTC](https://community.grafana.com/t/time-duration-calculation-between-rows/146450/14 "2025-03-17T18:48:34Z")

</div>

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).]

---

<div class="post-metadata">

**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\
**Post date:** [March 17, 2025, 6:49pm UTC](https://community.grafana.com/t/time-duration-calculation-between-rows/146450/15 "2025-03-17T18:49:30Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![acinvx](https://avatars.discourse-cdn.com/v4/letter/a/8baadc/32.png) [@acinvx](https://community.grafana.com/u/acinvx)\
**Post date:** [March 17, 2025, 6:57pm UTC](https://community.grafana.com/t/time-duration-calculation-between-rows/146450/16 "2025-03-17T18:57:36Z")

</div>

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  
}  
]

---

<div class="post-metadata">

**Author:** ![acinvx](https://avatars.discourse-cdn.com/v4/letter/a/8baadc/32.png) [@acinvx](https://community.grafana.com/u/acinvx)\
**Post date:** [March 18, 2025, 12:38am UTC](https://community.grafana.com/t/time-duration-calculation-between-rows/146450/17 "2025-03-18T00:38:43Z")

</div>

@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.

---

<div class="post-metadata">

**Author:** ![grant2](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/grant2/32/42281_2.png) [@grant2](https://community.grafana.com/u/grant2)\
**Post date:** [March 18, 2025, 1:20am UTC](https://community.grafana.com/t/time-duration-calculation-between-rows/146450/18 "2025-03-18T01:20:13Z")

</div>

Nice work @acinvx

I put your JSONata expression into [the dashboard](https://play.grafana.org/goto/HaLa7E2NR?orgId=1) and still am not getting it. Do I have the syntax correct? Maybe another “)”?

 ![image](https://us1.discourse-cdn.com/grafana/original/3X/9/b/9b5d6f0ffa9e19deeedcdd19ccc93cc8ff9225c8.png)

---

<div class="post-metadata">

**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\
**Post date:** [March 18, 2025, 1:27am UTC](https://community.grafana.com/t/time-duration-calculation-between-rows/146450/19 "2025-03-18T01:27:03Z")

</div>

this might help to get the juices flowing.

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

```

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

> **[Grafana](https://play.grafana.org/d/eeg515jterzswb/adriano-costa?orgId=1&from=now-6h&to=now&timezone=utc)**

---

<div class="post-metadata">

**Author:** ![acinvx](https://avatars.discourse-cdn.com/v4/letter/a/8baadc/32.png) [@acinvx](https://community.grafana.com/u/acinvx)\
**Post date:** [March 18, 2025, 3:37pm UTC](https://community.grafana.com/t/time-duration-calculation-between-rows/146450/20 "2025-03-18T15:37:06Z")

</div>

@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.

[Next page](https://community.grafana.com/t/time-duration-calculation-between-rows/146450.md?page=2)