CODEWITHSUNDEEP
mysql
suchislife
suchislife

Reputation: 1

Calculating time difference between of two rows when primary key is not consecutive. How?

After much review I have identified what I believe to be the problem with a working query.

The MySQL query below is used to get the time difference between 2 records in a table. Everything works fine if the table only has one user id AND primary keys are consecutive.

SELECT USER_ID,
       UTL_TASK,
       SUM(UTL_DURATION) AS UTL_DURATION_TOTAL       
  FROM (

SELECT A.PK_USER_TIME_LOG_ID,
       A.CLIENT_ID,
       A.PROJECT_ID,
       A.USER_ID,
       A.UTL_DTSTAMP,
       /* DATE_FORMAT(A.UTL_DTSTAMP,'%H:%i:%s') AS UTL_DTSTAMP, */
       A.UTL_LATITUDE,
       A.UTL_LONGITUDE,
       A.UTL_EVENT,
       A.UTL_TASK,
       /* DURATION in seconds  */
       TIMESTAMPDIFF(SECOND, A.UTL_DTSTAMP, B.UTL_DTSTAMP) AS UTL_DURATION
FROM   tbl_user_time_log A
       INNER JOIN tbl_user_time_log B
               ON B.PK_USER_TIME_LOG_ID = ( A.PK_USER_TIME_LOG_ID + 1 )
WHERE  A.USER_ID = '465615'
       /* Between current pay period Start date and Current pay period end date */
       /* First day of the week is Monday.*/
       AND ( A.UTL_DTSTAMP BETWEEN '2019-03-04' AND '2019-03-12' )
       /* Filter out Clock Out. */
       AND A.UTL_EVENT <> 'CLOCK OUT'

  /* Our derived table... */
) AS tbl_derived_1

GROUP BY USER_ID, UTL_TASK;

Working scenario sample:

working

Non Working scenario sample:

As you can see, the first orange entry in this example needs to subtract from the clock out entry stamp because it is the last stamp before clock out. Instead, the query is literally subtracting from the next in line???!!!

nonworking

How can I modify this query to take into account the following conditions:

A. The next consecutive record and respective time stamp inserted may or may not be added by the same user id. When it is not by the same user id, the query proceeds to just add the next record in line.

Basically, even when I try to filter by USER_ID 465615, the query just proceeds to add the next record in line whether it belongs to this user id or not.

Why?

Here's is the closest I was able to find on this site to possibly resolve this discrepancy.

https://stackoverflow.com/a/7937333/687137

Disclaimer: This link does not constitute a duplicate. If you're just reading similar english words and feel like down voting without truly understanding the difference between what's on the site and this specific question, move along.

Upvotes: 0

Views: 204

Answers (1)

Nick
Nick

Reputation: 147166

You should be able to just change your JOIN condition to:

INNER JOIN tbl_user_time_log B
    ON B.UTL_DTSTAMP = (SELECT MIN(UTL_DTSTAMP)
                        FROM tbl_user_time_log C
                        WHERE C.USER_ID = A.USER_ID AND C.UTL_DTSTAMP > A.UTL_DTSTAMP)
   AND B.USER_ID = A.USER_ID

This will mean that only consecutive (time-wise) rows belonging to the same user will be used for the TIMESTAMPDIFF computation.

If you are using MySQL 8+, you can use LEAD() and remove the JOIN altogether:

...
TIMESTAMPDIFF(SECOND, A.UTL_DTSTAMP, LEAD(UTL_DTSTAMP) OVER (PARTITION BY USER_ID ORDER BY UTL_DTSTAMP)) AS UTL_DURATION
FROM   tbl_user_time_log A
WHERE  A.USER_ID = '465615'
...

Upvotes: 2

Related Questions