In this example, we have a database that has various events, all stored with a uuid.

Our goal is to calculate the difference (in minutes) between the initial timestamp of the uuid and the first timestamp of the next record with the same uuid.

Right now, we're doing it manually in code with multiple SQL statements, which due to high usage is causing issues so we started caching results, however, the requirement is realtime.

Now I'm trying to figure out a way to get the database server to do the work in a single SQL statement.

I've tried various ways of joining results but no luck. There's probably some pattern magic someone knows that would go a long way in helping us figure this out. Any suggestions would be appreciated!

Taking 0df8-e817-050c-d0ce-06c0-53d6-8f99-c636 as an example, the lowest timestamp is:

2021-11-29 15:32:26.000000

And the first follow up timestamp is:

2021-11-29 15:39:52.000000

Seems simple enough to get the difference in minutes ... but a single SQL statement solution escapes me.

CodePudding user response：

For your version of MySql that does support window functions you can use a self join and aggregation to get the lowest and the next timestamps and with TIMESTAMPDIFF() get their difference:

SELECT t1.uuid, 
       TIMESTAMPDIFF(MINUTE, MIN(t1.stamp), MIN(t2.stamp)) diff
FROM tablename t1 LEFT JOIN tablename t2
ON t2.uuid = t1.uuid AND t1.stamp < t2.stamp
GROUP BY t1.uuid;

See the demo.