Calculating time difference between two following records

Grafana MySQL
1

Hi, I have a problem.
I’d like to calculate time duration between first and last value of selected period.
In the selected period I want to calculate time difference between actual record and previous record and summarize it.
When I calculate time difference between two datetime I get strange values:
This is the query:
SELECT
$__timeGroupAlias(time,$__interval),
temp_act,
temp_act - lag(temp_act) over (order by time) as “temp_change”,
time - lag(time) over (order by time) as “time_dif”,
max(time) - min(time) as “duration”
FROM e_kiln_dp5
WHERE $__timeFilter(time)
GROUP BY 1
ORDER BY $__timeGroup(time,$__interval)

The result:

Time temp_act temp_change time_dif duration
2023.09.19 17:10 40 0
2023.09.19 17:15 40 0 100 400
2023.09.19 17:20 40 0 500 400
2023.09.19 17:25 40 0 500 400
2023.09.19 17:30 56 16 500 400
2023.09.19 17:35 40 -16 500 400
2023.09.19 17:40 48 8 500 400
2023.09.19 17:45 56 8 500 400
2023.09.19 17:50 64 8 500 400
2023.09.19 17:55 65 1 500 400
2023.09.19 18:00 65 0 4500 400
2023.09.19 18:05 73 8 500 400

It can be seen that temp_change is calculated well but I do not undertand time_dif calculating.

  • the time difference between two timestamp is 5 min, which is 300 sec. Why 500 sec is calculated?
  • what does 4500 sec mean and how is it calculated?
  • what does duration mean and how is it calculated? What does max-min mean in this case?
2

What is your datasource?

3

MySQL

4

Can you see what this query gives you? Just trying to get 300 to appear in your “time_dif” column. The TIMESTAMPDIFF() function is used to calculate the difference in seconds between two timestamps.

SELECT
  $__timeGroupAlias(time,$__interval),
  temp_act,
  TIMESTAMPDIFF(SECOND, LAG(time) OVER (ORDER BY time), time) AS "time_dif",
  temp_act - LAG(temp_act) OVER (ORDER BY time) AS "temp_change"
FROM
  (
    SELECT
      $__timeGroupAlias(time,$__interval),
      temp_act
    FROM
      e_kiln_dp5
  ) AS t
GROUP BY 1
ORDER BY
  $__timeGroup(time,$__interval);

I may have some syntax issues in the above, so if you get an error, please try to debug before posting back.

1 Like
5

Thank you for your help.
Now it works well, the result is 300.
Only the first data (at the second record) is 60 instead of 300 and I don’t know why.

Question: why do you proposed this new FROM clausule?
I used this simple one:
FROM
e_kiln_dp5
where $__timeFilter(time)

6

That is to create a temporary table called t that contains the original data. It then uses the LAG() function to calculate the difference in time and temp_act between each sequential record. The TIMESTAMPDIFF() function is used to calculate the difference in seconds between two timestamps. The temp_act_diff column is calculated by subtracting the previous record’s temp_act from the current record’s temp_act.