Calculate the time differance between all TimeDate values and chart

Grafana MySQL
1

Grafana version 9.4.3

I have a query that returns two columns, a number and a time stamp. I would like to show instead calculate the time difference between the values, so this can be displayed as a bar chart highlighting the variation between each time stamp

image
image1835×338 20.8 KB

SELECT TimeDate, Step as value FROM dbo.AGVstep 
WHERE $__timeFilter(TimeDate) AND Program <> 0 AND Program in ($Program) and Step in ($Step) and AGV in ($AGV)
order by TimeDate asc

I would achieve this in excel by simply using each value and subtracting the previous

image
image898×587 18.4 KB

Can anyone advise how I can do this in Grafana?

Thanks

2

This is more of a sql query question than it is a grafana question.

That said what type of server is this? mysql, postgres?

3

mysql

4

Check out this function, try it out and if stuck let us know

1 Like
5

Thanks for the help with the LAG function.

SELECT TimeDate, 
       (DATEDIFF(SECOND, TimeDate, lag(TimeDate, 1) 
       OVER (ORDER BY TimeDate desc))) as CycleTime
  FROM AGVstep
  WHERE $__timeFilter(TimeDate) 
    AND Program <> 0 
    AND Program in ($Program) 
    AND Step in ($Step) and AGV in ($AGV)
  order by TimeDate asc

image
image1477×420 29.5 KB

Works as I hoped it would.

2 Likes