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 |
CodePudding user response：
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'
CodePudding user response：
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.