1

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? – a_horse_with_no_name Jan 27 '17 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) – Giorgio R Jan 27 '17 at 8:20
  • Well, in my example there is an error: the lowest time row should have ID 1 – Giorgio R Jan 27 '17 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 '17 at 13:06
2

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

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.