Timestampdiff on non-sequential rows in same table

Databases
1

The query I have thus far works fine as long as the rows are sequential. I know in real life they are not going to be but don’t quite understand how to go about dealing with non sequential rows… Is it possible in MySQL??

SELECT 
              h1.locID
		, h1.updated
		, h2.udpated
		, timestampdiff(second,h2.updated,h1.updated) AS secdiff
FROM 	  locations h1
INNER JOIN	 locations h2 ON h2.entryID = h1.entryID - 1
  ORDER BY	  h1.updated DESC
2

What do you mean sequential? You’re ordering by a date (at least I’m guessing h1.updated is a date, so the performance of the query should be consistent…

Edit:

Oohhhh. I understand what you mean now. You’re talking about the locID being sequential. What you’re going to run into is having your query stop the first time there’s a missing record in the locations table.

I’m not sure the purpose of the query - what exactly are you trying to accomplish here?

3

@ DaveMaxwell - Sorry Dave, Its the primary that is not sequential… What I’m ‘Trying’ to achieve is a severity and duration of a delay. The There are four delay severities similar to BRAG on an issue log. Every time the severity changes a row is added to the table. I have the previous Severity, the current severity and the datetime it was updated. So, if I can get the timestampdiff from current row to previous I can work out in seconds how long that severity lasted…God I hope all that makes sense…It’s clear in my head but that’s about the only thing that is…

4

if you expect auto_increments to be consecutive, “ur doing it wrong” as the kids say

the only thing guaranteed about an auto_increment is that it will by unique, full stop

SELECT h1.locID , h1.updated , h2.udpated , TIMESTAMPDIFF(second,h2.updated,h1.updated) AS secdiff FROM locations h1 INNER JOIN locations h2 ON h2.entryID = ( SELECT MAX(entryID) FROM locations WHERE entryid < h1.entryid ) ORDER BY h1.updated DESC

5

@r937 - Totally understand. I negated to include the primary key field in my question, sorry about that. Your reply, as always is such a great help. Thanks

6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.