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

Question

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?

Asgar · Accepted Answer · 2022-12-03T06:20:52.820

2

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;

edited Dec 03 '22 at 06:20

answered Dec 03 '22 at 06:14

Asgar

1,920
2
8
17

How can I order the list when querying by date (ORDER BY), not to use the necessary id to order the sequence of bookings? – Cole Dec 03 '22 at 06:30
Is `id` not your primary key? How do you want your result to be ordered by? – Asgar Dec 03 '22 at 06:31
Yes @Asgar, but I'm thinking if a line were removed to delete an event and a new event was added with a similar time to the deleted one in other `id`, it would perhaps generate a "hole". – Cole Dec 03 '22 at 06:38
Use my second query with Windows Function – Asgar Dec 03 '22 at 06:39
Both codes worked perfectly, I'm just thinking about the order, because if `id 2` was deleted for example and re-added as `id 6`, it would create a difference when calculating the difference between the endTime of `id 5` and the startTime of `id 6`, would sorting by growing list fix this? – Cole Dec 03 '22 at 06:44
Are you talking about manually editing your primary key from 2 to 6? – Asgar Dec 03 '22 at 06:48
Not manually, say if for example the line is deleted (to delete the event) and a new one is added with similar data, this new event by (Auto Increment) will be added last. – Cole Dec 03 '22 at 06:49
`ORDER BY startTime ASC` something like that, normally works in? – Cole Dec 03 '22 at 06:53
Yes it works that way – Asgar Dec 03 '22 at 06:54
Stick with the [LEAD() window function](https://mariadb.com/kb/en/lead/) (available since MariaDB 10.2) and do not use the id sequence. It may work for the most naïve of test cases but it will not work with real world data. Take this very simple example - cust 1 calls up and books for next Friday afternoon (id: 6, startTime: 2022-12-16 16:30). Next, cust 2 calls up and books for this afternoon (id: 7, 2022-12-05 15:00). Using a join on `t1.id + 1 = t2.id` is not going to build a logical time sequence! – user1191247 Dec 05 '22 at 13:32

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

1 Answers1