How to calculate the smallest period of time between consecutive events?

Question

Lets say that I have a table with a timestamp column full of records and I want to calculate the smallest time difference between two consecutive records using only one query.

Maybe a table like...

CREATE TABLE `temperatures` (
  `temperature` double,
  `time` timestamp DEFAULT CURRENT_TIMESTAMP
);

Answer 1

You could try this:

SELECT
    T1.*,
    (SELECT MIN(T2.time)
     FROM temperatures T2
     WHERE T2.time > T1.time)-T1.time diff
FROM
    temperatures T1
ORDER BY
    T1.time

Answer 2

Try a query like this:

select 
    cur.timestamp as CurrentTime,
    prev.timestamp as PreviousTime,
    timediff(cur.timestamp,prev.timestamp) as TimeDifference,
    cur.temperature - prev.temperature as TemperatureDifference
from temperatures cur
left join temperatures prev on prev.timestamp < cur.timestamp
left join temperatures inbetween
    on prev.timestamp < inbetween.timestamp
    and inbetween.timestamp < cur.timestamp
where inbetween.timestamp is null

The first join seeks all previous rows for the current ("cur") row. The second join seeks rows in between the first and the second row. The where statement says there cannot be any rows in between the first and the second row. That way, you get a list of rows with their preceeding row.

Answer 3

What you need is analytical functions LAG and MIN.

They are missing in MySQL, but can be easily emulated using session variables.

This query returns all differences between consecutive records:

SELECT  (temperature - @r) AS diff,
        @r := temperature
FROM    (
        SELECT  @r := 0
        ) vars,
        temperatures
ORDER BY
        time

This one returns minimal time difference:

SELECT  (
        SELECT  id,
                @m := LEAST(@m, TIMEDIFF(time, @r)) AS mindiff,
                @r := time
        FROM    (
                SELECT  @m := INTERVAL 100 YEAR,
                        @r := NULL
                ) vars,
                temperatures
        ORDER BY
                time, id
        ) qo
WHERE   qo.id = 
        (
        SELECT  id
        FROM    temperatures
        ORDER BY
                time DESC, id DESC
        LIMIT 1
        )

See this article in my blog on how to emulate analytic functions in MySQL:

• Analytic functions: FIRST_VALUE, LAST_VALUE, LEAD, LAG

If you add a PRIMARY KEY to you table (which you should always, always do!), then you may use more SQL-ish solution:

SELECT  temperature -
        (
        SELECT temperature
        FROM   temperatures ti
        WHERE  (ti.timestamp, ti.id) < (to.timestamp, to.id)
        ORDER BY
               ti.timestamp DESC, ti.id DESC
        LIMIT 1
        )
FROM    temperatures to
ORDER BY
       to.timestamp, to.id

This solution, though, is quite inefficient in MySQL due to the bug 20111.

The subquery will not use the range access path, though it will use an index on (timestamp, id) for ordering.

This may be worked around by creating a UDF that returns previous temperature, given the current record's id.

See this article in my blog for details:

• Analytic functions: optimizing LAG, LEAD, FIRST_VALUE, LAST_VALUE

IF you don't use any filtering conditions, then the solution which uses session variable will be the most efficient, though MySQL specific.

Similar solutions for SQL Server will look like this:

SELECT  temperature -
        (
        SELECT TOP 1 temperature
        FROM   temperatures ti
        WHERE  ti.timestamp < to.timestamp
               OR (ti.timestamp = to.timestamp AND ti.id < to.id)
        ORDER BY
               ti.timestamp DESC, ti.id DESC
        )
FROM    temperatures to
ORDER BY
       to.timestamp, to.id

and

SELECT  MIN(mindiff)
FROM    (
        SELECT  timestamp -
                (
                SELECT TOP 1 timestamp
                FROM   temperatures ti
                WHERE  ti.timestamp < to.timestamp
                       OR (ti.timestamp = to.timestamp AND ti.id < to.id)
                ORDER BY
                       ti.timestamp DESC, ti.id DESC
                ) AS mindiff
        FROM    temperatures to
        ORDER BY
               to.timestamp, to.id
        ) q

In SQL Server, this will work OK, provided you have an index on (timestamp, id) (or just on (timestamp), if your PRIMARY KEY is clustered)

Answer 4

Assuming that there is a unique constraint on the time stamp (to prevent there being two recordings at the same time):

SELECT MIN(timediff(t1.`time`, t2.`time`)) AS delta_t,
    FROM temperatures t1 JOIN temperatures t2 ON t1.`time` < t2.`time`

This answers the questions rather precisely - and doesn't convey other useful information (such as which two timestamps or temperatures).