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.
time
or theid
column?