I have a table which logs the PID tuning parameters for several loops. Whenever a user changes any of these values, I log the new value and the t_stamp. The client wants to know the time between parameter changes. I have concocted a few SQL queries thay may get me where I need to go but none are very efficient or elegant. Does anyone know of a efficient what to return the time between two values changing in a SQL table?
Your looking for a timediff function (assuming the time is stored as a time not an int) This will be different for each db type (MSSQL,SQLite,MySQL, etc) but it would look something like this:
SELECT timediff(t1.time, t2.time)
FROM tablename t1, tablename t2
WHERE t1.id = (search criteria for first record)
AND t2.id = (search criteria for second record)
By creating the aliases t1 and t2, you can pull two different records from the same table. Then it’s a simple mater of finding the correct time comparison function and syntax for your database.
mysql uses timediff. MSSQL does not know that.
So if you are using MSSQL here are some options.
You could use a join with DATEDIFF.
msdn.microsoft.com/en-us/library/ms189794.aspx
SELECT TOP 1 DATEDIFF(s,n2.timestamp,n.timestamp) AS diff
FROM tablename t
LEFT OUTER JOIN tablename t2
ON t2.ndx = (t.ndx - 1)
ORDER BY t.timestamp DESCThis would give you the time difference in seconds. You could change that to minutes or hours easily by altering the DATEDIFF function. You could also have it return hh:mi.
Not sure what you are looking for.
OR you could you a COALESCE function
SELECT TOP 1 COALESCE(t.timestamp - (SELECT t2.timestamp from tablename t2 WHERE t2.ndx = t.ndx -1), 0) as diff
FROM tablename t
ORDER BY timestamp DESCYou can add WHERE clauses to either to replace the TOP 1 function I added like Robert suggested.
Cheers,
Chris
In PostgreSQL you could use window functions.
I just recently wrote some queries to calculate the rate of change of a variable which needed to know the length of time between data rows.