I have a table (called
ReqTime DATETIME NOT NULL column AND
Duration INT UNSIGNED column.
Duration should be set to the difference of
ReqTime for two adjacent rows. But
Duration is not set when inserting new rows to the table. It is calculated afterward.
I calculate the "duration" of a row R as the interval (in seconds) between R.ReqTime and N.ReqTime where N is the next row (first row, inserted later the current row).
Thus for each row R (except of the last inserted row, as for it N is undefined) we have
See pseudocode for updating the table with the correct
Duration value (where R is the current row and N is the next (inserted later) row):
UPDATE Visits SET R.Duration=TIMEDIFF(N.ReqTime, R.ReqTime) WHERE R.Duration IS NULL
Should I use cursors to solve this problem? Or are MIN/MAX/ORDER BY fine?
I am not yet comfortable with cursors.