Calculate difference between endTime from ID 1 and startTime from ID 2-CodePudding

id   startTime            endTime 
1    2022-12-3 13:00:00   2022-12-3 14:00:00
2    2022-12-3 14:00:00   2022-12-3 14:30:00
3    2022-12-3 15:00:00   2022-12-3 15:15:00 
4    2022-12-3 15:30:00   2222-12-3 16:30:00
5    2022-12-3 18:30:00   2022-12-3 19:00:00


SELECT startTime, endTime, 
(TIMESTAMPDIFF(MINUTE, startTime , endTime) = '60') AS MinuteDiff 
FROM booking

OUTPUT:

    id   startTime            endTime             MinuteDiff
    1    2022-12-3 13:00:00   2022-12-3 14:00:00  1
    2    2022-12-3 14:00:00   2022-12-3 14:30:00  0
    3    2022-12-3 15:00:00   2022-12-3 15:15:00  0
    4    2022-12-3 15:30:00   2022-12-3 16:30:00  1
    5    2022-12-3 18:30:00   2022-12-3 19:00:00  0

I am calculating the difference between the startTime and endTime of ID 1, how to calculate the difference between the endTime of ID 1 and the startTime of ID 2, and so on?

CodePudding user response：

Do try this one: If you want your last row to be included in your result, use LEFT JOIN, if you don't want to include the last row use 'JOIN'.

SELECT d.`id`, 
d.`endTime`, 
IFNULL(d1.`startTime`,d.`endTime`),
IFNULL(TIMESTAMPDIFF(MINUTE, d.endTime, d1.startTime),0) FROM date_table d  LEFT 
JOIN date_table d1 ON d1.`id`=d.`id` 1

Or you can use following with Windows Functions:

SELECT 
    id,
    endTime,
    lead(startTime) over (order by id) nextStartDate,
    TIMESTAMPDIFF(MINUTE,endTime,lead(startTime) over (order by id)) as timeDiff
  FROM 
  date_table d;

CodePudding user response：

To calculate the difference between the end time of one row and the start time of the next row in a table, you can use a self-join in your SQL query. A self-join is used to join a table to itself, allowing you to compare values in the same table.

Here is an example of how you could use a self-join to calculate the difference between the end time of one row and the start time of the next row in your table:

SELECT b1.startTime, b1.endTime, b2.startTime,
TIMESTAMPDIFF(MINUTE, b1.endTime, b2.startTime) AS MinuteDiff
FROM booking b1
INNER JOIN booking b2
ON b1.id = b2.id - 1

In this query, we use a self-join to join the booking table to itself. We use the id column to join the two copies of the table, and we subtract 1 from the id of the second copy of the table to ensure that we are comparing the end time of one row with the start time of the next row.

We then use the TIMESTAMPDIFF function to calculate the difference in minutes between the end time of the first row and the start time of the second row. This function will return the difference between the two times for each pair of rows in the table.

The output of this query will be a table with the start time and end time of each row, along with the start time of the next row and the difference in minutes between the end time of the current row and the start time of the next row. This will allow you to see the difference in time between each pair of rows in the table.