sqlmariadb-10.4

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

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?

Solution

  • 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;