Finding largest time interval between list of dates

Question

I have a table of items, each with a timestamp connected to it. I want to find the largest interval between any pair of consecutive items. Is this possible with a query only?

---

UPDATE: Let me see if I can make this a little less confusing. What I want to do is compare each items timestamp to the item before and after it, to find out how much time has passed between the two times. I want to do that for all the items, and at the end have the largest difference between two items.

I dont want the largest difference between any two items because that would obviously be the first and last item. Hopefully that description is clearer.

---

UPDATE2: So quickly after accepting an answer, I realized I again wrongly described the problem. The new issue is that the items ids aren't in the same order as the timestamp.

I'm going to try to describe the problem with some data...

Item - Time (seconds)
Item0 - 000
Item1 - 030
Item2 - 120
Item3 - 090
Item4 - 100

So for the items above, I want 60 seconds (between item1 and 3) but Martin's (great) answer would return 90 seconds (item1 and 2)

Answer 1

Will this work?

select max(timestamp_field)-min(timestamp_field) from my_table;

Answer 2

Assuming table like:

create table item ( i int not null primary key, 
                    t datetime not null );

select i1.i, i2.i, 
       abs( unix_timestamp( i1.t ) - unix_timestamp( i2.t )) diff 
from item i1
join item i2 on i1.i+1 = i2.i 
order by diff desc
limit 1;

This is a self-join where a row is joined with the next row. The diff value is printed in descending value (largest first), and only the first row is output.

EDIT: Here's a feeble attempt to salvage my solution given the clarification of the question.

Create a temporary table with the entries datetime order, assigned an auto_increment value representing the ranking, then apply the self-join to the temporary table:

create temporary table ranked_item ( rank int not null auto_increment primary key, 
                    i int not null, 
                    t datetime not null );

insert into ranked_item 
       select null, i, t from item order by t asc;

select i1.rank, i2.rank, i1.i, i2.i, 
       abs( unix_timestamp( i1.t ) - unix_timestamp( i2.t )) diff 
from ranked_item i1
join ranked_item i2 on i1.rank+1 = i2.rank 
order by diff desc
limit 1;

Obviously, if your item table is huge, this could be a bit extravagant.

Answer 3

Since you didn't list any table names in the question, I made up times_logged_in.log_in to find a solution. Also, I don't have MySQL installed, so I tried this in MS SQL Server, but it should be pretty close.

select top 1
    tli2.log_in as log_in,
    tli2.previous as previous,
    tli2.log_in - tli2.previous as time_span
from
    (
        select
            tli.log_in as log_in,
                (select max(log_in) from times_logged_in p where p.log_in < tli.log_in) as previous
        from
            times_logged_in tli
    ) tli2
order by time_span desc

Just to clarify, this is done as follows:

First we select all the times (and any other columns that we want in the output, like IDs or whatever). This is the second SELECT above.

Then we add a column for the previous time. This is found by using a correlated subquery. This is the third SELECT above.

Then, we calculate the timespans between the time and the previous time returned from our query. We do this by wrapping the whole thing up in another query. This is the first SELECT above. So now we have whatever columns we want plus the previous time and the timespan between them. So we just order the rows descending by the timespan length and take the first one, and we're done!