# Time Between Value Changes in SQL

**URL:** <https://forum.inductiveautomation.com/t/time-between-value-changes-in-sql/6576>\
**Category:** Archived Topics\
**Created:** [May 24, 2013, 7:04pm UTC](https://forum.inductiveautomation.com/t/time-between-value-changes-in-sql/6576 "2013-05-24T19:04:05Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![bhastie](https://avatars.discourse-cdn.com/v4/letter/b/d6d6ee/32.png) [@bhastie](https://forum.inductiveautomation.com/u/bhastie)\
**Post date:** [May 24, 2013, 7:04pm UTC](https://forum.inductiveautomation.com/t/time-between-value-changes-in-sql/6576/1 "2013-05-24T19:04:05Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Robert](https://avatars.discourse-cdn.com/v4/letter/r/e95f7d/32.png) [@Robert](https://forum.inductiveautomation.com/u/Robert)\
**Post date:** [May 24, 2013, 9:37pm UTC](https://forum.inductiveautomation.com/t/time-between-value-changes-in-sql/6576/2 "2013-05-24T21:37:55Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![CPowell](https://avatars.discourse-cdn.com/v4/letter/c/a183cd/32.png) [@CPowell](https://forum.inductiveautomation.com/u/CPowell)\
**Post date:** [May 24, 2013, 10:10pm UTC](https://forum.inductiveautomation.com/t/time-between-value-changes-in-sql/6576/3 "2013-05-24T22:10:32Z")

</div>

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](http://msdn.microsoft.com/en-us/library/ms189794.aspx)

```auto
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 DESC
```

This 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

```auto
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 DESC
```

You can add WHERE clauses to either to replace the TOP 1 function I added like Robert suggested.

Cheers,  
Chris

---

<div class="post-metadata">

**Author:** ![malleblas](https://avatars.discourse-cdn.com/v4/letter/m/ea666f/32.png) [@malleblas](https://forum.inductiveautomation.com/u/malleblas)\
**Post date:** [January 22, 2014, 6:47am UTC](https://forum.inductiveautomation.com/t/time-between-value-changes-in-sql/6576/4 "2014-01-22T06:47:36Z")

</div>

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.