# Calculating time difference between two following records

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?

MySQL

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

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.