Question 113242 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Calculate difference in row values based on other row values in SQL

I want to calculate the difference between endTime and startTime for their corresponding IDs
and display ID and duration(calculated field) without having to create a new table in SQL.

I have tried ordering the table and using LAG and LEAD but that gives the difference of all rows.

ID TypeTime DateTime
1 startTime April 26, 2022, 12:38 PM
1 endTime April 26, 2022, 12:39 PM
2 startTime April 26, 2022, 12:45 PM
2 endTime April 26, 2022, 12:49 PM
3 startTime April 28, 2022, 11:24 PM
3 endTime April 28, 2022, 11:30 PM

The result should look something like this:

ID Duration (s)
1 60
2 240
3 360
Tags:

2

Answers


  1. 0

    Use a self-join:

    SELECT t1.id, TIMESTAMPDIFF(second, t1.datetime, t2.datetime) AS duration
FROM yourTable AS t1
JOIN yourTable AS t2 ON t1.id = t2.id
WHERE t1.typetime = 'startTime' AND t2.typetime = 'endTime'
    Login or Signup to reply.
  2. 0

    Supposing that for each ID there is a start and end time, you may use min and max functions as the following:

    SELECT id, TIMESTAMPDIFF(SECOND, MIN(DateTime), MAX(DateTime)) AS duration
FROM 
table_name
GROUP BY id
ORDER BY id

    See a demo.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search