mysql query help

[dipti.bhuyan]

i have a table with a date column which is having records as follows.
2008-04-12 3.30.00
2008-04-12 3.30.10
2008-04-12 3.30.20
2008-04-12 3.30.30
2008-04-12 3.31.10
2008-04-12 3.31.20
2008-04-12 3.31.30
2008-04-12 3.31.50
2008-04-12 3.32.00
2008-04-12 3.32.40

i need a query to show results like below.
2008-04-12 3.30.00 30
2008-04-12 3.31.10 20
2008-04-12 3.31.50 10

if time diff between sucessive rows 10 secs then take the least time & calculate cumulative intervals .

please provide me a query.i will appreciat your help.

regards,
Dipti

[Old Pedant]

Does this table have an AUTONUMBER column? Or, if it doesn't have one, can you add one?

Do you have a reason that this *MUST* be done in a SQL query?? It would be pretty trivial to do in PHP/JSP/ASP coding.

Even *with* an AUTONUMBER column, this will not be at all easy to do in SQL.

[dipti.bhuyan]

thanks a lot
i need a direct query that will get me the result.sothat i dont have to iterate it again to filter out & calculate the time gap.autonumber wont help me as same kind of sets may be inserted based on location id,the output given in my post is per location id.

Does this table have an AUTONUMBER column? Or, if it doesn't have one, can you add one?

Do you have a reason that this *MUST* be done in a SQL query?? It would be pretty trivial to do in PHP/JSP/ASP coding.

Even *with* an AUTONUMBER column, this will not be at all easy to do in SQL.

[Old Pedant]

With those conditions, I think you *WILL* have to "iterate it again to filter out & calculate the time gap."

The only difference is that you might do that in a pretty complex stored procedure (a really ugly one, using a cursor and, I believe, a temp table...so won't even be very fast) instead of in really very very simple PHP/ASP/JSP code.

I really do recommend that you reconsider doing it all in SQL.

[dipti.bhuyan]

yes i do think it will be clean code if i do it in jsp or java.for sake is it possible through a query.just being curious ,if at all possible through a query like self joining etc without any procedures or temp tables etc.
thx for your all help

[Old Pedant]

I think their would be a way if the records were sequentially numbered. Basically, you would look for the first record to mismatch on the numbering vs. the time. Even then it would be a pretty complex query.

But without that, the only way I see is via a cursor and a temp table.