I have a table with these two columns (plus some others that are not relevant):
transaction_pk and value_timestamp
I would like to get the time difference (in minutes) for each transaction_pk. Each transaction_pk has many rows and i'm not looking for difference between 1st and last timestamp
Example table
transaction_pk | status_timestamp
1 | 2020-02-11 19:14:45
1 | 2020-02-11 19:18:45
1 | 2020-02-11 19:15:45
2 | 2020-02-11 19:18:45
2 | 2020-02-11 19:14:45
2 | 2020-02-11 19:19:45
The desired output would look like this:
transaction_pk | status_timestamp | time_diff
1 | 2020-02-11 19:14:45 | 0
1 | 2020-02-11 19:18:45 | 04:00
1 | 2020-02-11 19:20:45 | 02:00
2 | 2020-02-11 19:18:45 | 0
2 | 2020-02-11 19:14:45 | 04:00
2 | 2020-02-11 19:19:45 | 05:00
I have tried the following question:
Calculate the difference in minutes between timestamp mysql
but I get the following error message when I try and execute the code
#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'report_db.x.connector_pk' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
*Edit - this now works after running the following:
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
It does however take a long time to run. Is there a way to make it quicker and also to update my table rather than just run a query.
I'm not a fan of putting subqueries in the SELECT but it's a way of solving this problem for ancient versions of MySQL:
You can use it like you would any other value in e.g. a call to timestampdiff:
The COALESCE converts the NULL that results from the first value per PK not having any "timestamp less than the current", into a 0.. otherwise, the idea of diffing on the seconds then dividing by 60 gives us a decimal representation of the number of fractional minutes since the previous time. If you need greater precision, use a smaller time interval and a greater divisor respectively