# Comparing previous/next record to calculate an average speed

I have a simple table ina MySQL db recording the speed in m/s of a production machine every n seconds.

``````ID    SPEED    TIME
----------------------------------
1     2.5      2017-01-27 08:23:03
2     1.9      2017-01-27 08:22:02
3     2.0      2017-01-27 08:21:02
4     2.1      2017-01-27 08:20:01
5     2.3      2017-01-27 08:19:03
``````

ID is an auto increment field.

Due to some lacks in signal transmission, the time interval between two rows is not always the same. What I would do is:

1. Calculate the difference of time between two rows (assuming that in that timeframe speed is constant);
2. Multiply the calculated timeframe for the speed (so I get a distance);
3. Sum the results (I get the total distance);
4. Calculate the difference in seconds between the first and last records selected (I get the total time);
5. Divide total distance for total time getting my average speed.

My problem is to understand how to get previous and next record and how to do that iteratively.

This is now in MySQL, but will be moved to Postgres soon.

• "next" and "previous" row only makes sense if you define an ordering. Should that be based on the `time` or the `id` column?
– user1822
Jan 27, 2017 at 7:50
• Ideally, it should be based on time, but at the end of the day is the same thing (ID is auto increment) Jan 27, 2017 at 8:20
• Well, in my example there is an error: the lowest time row should have ID 1 Jan 27, 2017 at 9:07
• Note that what you are doing is numerical integration (en.wikipedia.org/wiki/Numerical_integration). There are various ways to do that - most simple are rectangle rule and trapezoidal rule. Of these, trapezoidal rule is already a lot more accurate, and is equally simple to implement; at step 2, multiply by the average speed of the two rows.
– jpa
Jan 27, 2017 at 13:06

This covers the first point of your question. Notice I've ordered by TIME1 desc due you need to emulate LAG() function.

Notice also I've used TIME1 instead of TIME as a field name.

``````select ID, SPEED, t1, t2, TIME_TO_SEC(TIMEDIFF(t2, t1)) T_DIF,
(TIME_TO_SEC(TIMEDIFF(t2, t1)) * SPEED) AS DIST
from (
select ID, SPEED, @t1 as t2, @t1 := TIME1 as t1
from
(select @t1 := MAX(TIME1) from ss) x,
(select ID, SPEED, TIME1 from ss order by TIME1 desc) y
) z
;
``````

This sentence returns next values:

``````+----+-------+---------------------+---------------------+-------+-------+
| ID | SPEED |          t1         |          t2         | T_DIF | DIST  |
+----+-------+---------------------+---------------------+-------+-------+
| 1  | 2,5   | 2017-01-27 08:23:03 | 2017-01-27 08:23:03 | 0     | 0,0   |
+----+-------+---------------------+---------------------+-------+-------+
| 2  | 1,9   | 2017-01-27 08:22:02 | 2017-01-27 08:23:03 | 61    | 115,9 |
+----+-------+---------------------+---------------------+-------+-------+
| 3  | 2,0   | 2017-01-27 08:21:02 | 2017-01-27 08:22:02 | 60    | 120,0 |
+----+-------+---------------------+---------------------+-------+-------+
| 4  | 2,1   | 2017-01-27 08:20:01 | 2017-01-27 08:21:02 | 61    | 128,1 |
+----+-------+---------------------+---------------------+-------+-------+
| 5  | 2,3   | 2017-01-27 08:19:03 | 2017-01-27 08:20:01 | 58    | 133,4 |
+----+-------+---------------------+---------------------+-------+-------+
``````

And using this values you can calculate average speed:

``````select TIME_TO_SEC(TIMEDIFF(MAX(t2), MIN(t1))) DIF_T,
SUM((TIME_TO_SEC(TIMEDIFF(t2, t1)) * SPEED)) TOTAL_DIST,
(SUM((TIME_TO_SEC(TIMEDIFF(t2, t1)) * SPEED)) / TIME_TO_SEC(TIMEDIFF(MAX(t2), MIN(t1)))) AVG_SPEED
from (
select ID, SPEED, @t1 as t2, @t1 := TIME1 as t1
from
(select @t1 := MAX(TIME1) from ss) x,
(select ID, SPEED, TIME1 from ss order by TIME1 desc) y
) z
;

+-------+------------+-----------+
| DIF_T | TOTAL_DIST | AVG_SPEED |
+-------+------------+-----------+
| 240   | 497,4      | 2,07250   |
+-------+------------+-----------+
``````

Check it here: http://rextester.com/YYYHE22690

Getting the previous row is very easy in Postgres:

``````select id,
speed,
"time",
extract(epoch from lag("time") over (order by "time") - "time") as diff_seconds,
from the_table
order by "time";
``````

The whole calculation would be:

``````select extract(epoch from max("time") - min("time")) as total_seconds,
sum(diff_distance) / extract(epoch from max("time") - min("time")) as avg_speed
from (
select id,
speed,
"time",
extract(epoch from lag("time") over (order by "time") - "time") as diff_seconds,
speed * extract(epoch from lag("time") over (order by "time") - "time") as diff_distance
from the_table
order by "time"
) t;
``````

Subtracting a timestamp from a timestamp yields an `interval`. Using `extract(epoch ...)` converts the interval to the total number of seconds in that interval.

The column `diff_distance` isn't really necessary, I just added that for debugging purposes.

This however is currently not possible in MySQL.

• "currently" as in "eternity" :-) Jan 27, 2017 at 7:55
• @DuduMarkovitz: not sure actually. MySQL 8.0 will finally have common table expression: mysql.de/why-mysql/presentations/… and MariaDB 10.2 does indeed have window functions
– user1822
Jan 27, 2017 at 8:01