Diff values in datetime

Ok, some test data first.
We make a point of using a non-significant and possibly non-ordered key field because we want to show that you don't need to introduce an additional ordering/indexing field.

create table sheila
(
  id int,
  eventTime datetime
)

set nocount on
declare @startPoint datetime 
set @startPoint = getdate()
insert into sheila(id, eventTime) values (120, @startPoint)
insert into sheila(id, eventTime) values (125, dateadd(mi, 45, @startPoint))
insert into sheila(id, eventTime) values (131, dateadd(hh, 6, @startPoint))
insert into sheila(id, eventTime) values (133, dateadd(dd, 1, @startPoint))
insert into sheila(id, eventTime) values (165, dateadd(dd, 2, @startPoint))
insert into sheila(id, eventTime) values (765, dateadd(dd, 6, @startPoint))

-- Show the raw data.
select * from sheila

Now work out the time difference in seconds between each event and the one that precedes it.

select 
  sheila.id, 
  sheila.eventTime as [curr],
  max(coalesce(prev.eventTime, sheila.eventTime)) as [prev], 
  datediff(ss, 
           max(coalesce(prev.eventTime ,sheila.eventTime)), 
           sheila.eventTime) as [diffSeconds]
from sheila
left join sheila as prev 
      on prev.eventTime < sheila.eventTime
group by sheila.id, sheila.eventTime

The raw data looks like this...

id          eventTime
----------- -----------------------
120         2013-11-16 10:55:51.423
125         2013-11-16 11:40:51.423
131         2013-11-16 16:55:51.423
133         2013-11-17 10:55:51.423
165         2013-11-18 10:55:51.423
765         2013-11-22 10:55:51.423

...and our result like this:

id          curr                    prev                    diffSeconds
----------- ----------------------- ----------------------- -----------
120         2013-11-16 10:55:51.423 2013-11-16 10:55:51.423 0
125         2013-11-16 11:40:51.423 2013-11-16 10:55:51.423 2700
131         2013-11-16 16:55:51.423 2013-11-16 11:40:51.423 18900
133         2013-11-17 10:55:51.423 2013-11-16 16:55:51.423 64800
165         2013-11-18 10:55:51.423 2013-11-17 10:55:51.423 86400
765         2013-11-22 10:55:51.423 2013-11-18 10:55:51.423 345600

A word of warning. It is assumed that you never have events with _identical_ timestamps.

If it's not obvious how it works...
The first step is to work out how to get all the events earlier than the current event,
so we have the self-join.

So for every event we can now display the times of all previous events. However we want only the latest. The max(imum) of the previous so we ask for exactly that using the max() function. We can then do the arithmetic using the datediff function.

The names here are T-SQL specific but I would expect mysql to offer something similar.

If you wanted to throw away the very first event (ie diff is zero) you would use an inner join rather than a left join.

See what happens without the max() and grouping.

select 
  sheila.id, 
  sheila.eventTime as [curr],
  prev.eventTime as [earlier]
from sheila
left join sheila as prev 
      on prev.eventTime < sheila.eventTime
order by sheila.eventTime

id          curr                    earlier
----------- ----------------------- -----------------------
120         2013-11-16 10:55:51.423 NULL
125         2013-11-16 11:40:51.423 2013-11-16 10:55:51.423
131         2013-11-16 16:55:51.423 2013-11-16 10:55:51.423
131         2013-11-16 16:55:51.423 2013-11-16 11:40:51.423
133         2013-11-17 10:55:51.423 2013-11-16 10:55:51.423
133         2013-11-17 10:55:51.423 2013-11-16 11:40:51.423
133         2013-11-17 10:55:51.423 2013-11-16 16:55:51.423
165         2013-11-18 10:55:51.423 2013-11-16 10:55:51.423
165         2013-11-18 10:55:51.423 2013-11-16 11:40:51.423
165         2013-11-18 10:55:51.423 2013-11-16 16:55:51.423
165         2013-11-18 10:55:51.423 2013-11-17 10:55:51.423
765         2013-11-22 10:55:51.423 2013-11-16 10:55:51.423
765         2013-11-22 10:55:51.423 2013-11-16 11:40:51.423
765         2013-11-22 10:55:51.423 2013-11-16 16:55:51.423
765         2013-11-22 10:55:51.423 2013-11-17 10:55:51.423
765         2013-11-22 10:55:51.423 2013-11-18 10:55:51.423

The coalesce function is used to give sensible output for our first event and to suppress
the "null suppressed in aggregate" warnings we would otherwise see.