ojdajuiceman ojdajuiceman - 6 months ago 39
SQL Question

Find difference between sequential rows mysql, no row ID

Objective: get the difference between the value in a row and the value in the next row (I'm using MySQL). Say we have the table "events":

step: timestamp:

Leave for store 1400000000
Buy hamburgers 1400000002
Big party 1400000005


So the result we'd expect is:

2
3


Complication 1: My table doesn't have an ID column, so I can't do this:

select (e2.timestamp - e1.timestamp)
from events e1, events e2
where (e1.id + 1) = e2.id


Complication 2: I'm using a database connection (Splunk) that won't allow me to create or alter temporary tables (otherwise I'd just add an id column). Am I hosed?

thank you!

Answer

Use a user variable to hold the timestamp from the previous line.

SELECT step, timestamp - @prevtime AS diff, @prevtime := timestamp
FROM events
CROSS JOIN (SELECT @prevtime := 0) AS x
ORDER BY timestamp