current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I have a table (called Visits) with 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 Duration value.

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.

MySQL 5.

share|improve this question
    
Search about emulating the LAG function with user variables. –  Mihai 10 hours ago
1  
Don't bother learning CURSORS; in over 30 years as a database applications developer I have never once used one, except by way of demonstrating that they were unnecessary and non-performant. –  Pieter Geerkens 9 hours ago

3 Answers 3

up vote 2 down vote

This SQL works fine in SQL Server (with appropriate syntax modifications):

UPDATE Visits
SET Duration = TimeDiff(
        ,( SELECT ReqTime FROM Visits N WHERE n.ReqTime > R.ReqTime ORDER BY ReqTime LIMIT 1)
        ,R.ReqTime
    )
FROM Visits R
share|improve this answer
    
@user46591: Typo corrected for Timeiff. –  Pieter Geerkens 5 hours ago
up vote 0 down vote

@pieter-geerkens:

Timeiff? :-)

share|improve this answer
    
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post. –  Paul White 8 hours ago
up vote 0 down vote

if reqtime is inserted as now(), you can use now()-max(reqtime), otherwise Pieter's answer is great.

share|improve this answer
    
It's on a previously inserted row. You know that it's no longer the same now() –  Ben 3 hours ago
    
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post. –  Colin 't Hart 1 hour ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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