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:


Calculate the difference of time between two rows (assuming that in 
that timeframe speed is constant);
Multiply the calculated timeframe for the speed (so I get a distance);
Sum the results (I get the total distance);
Calculate the difference in seconds between the first and last records selected (I get the total time);
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.
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.