Q: Calculate diff time in MySQL between to status records This is one of my MySQL tables:




idpk
id
status
timestamp




1
43857
AVAILABLE
2023-01-07 09:14:19


2
43857
OFFLINE
2023-01-07 18:14:19


3
43857
AVAILABLE
2023-01-07 23:14:19


4
43860
AVAILABLE
2023-01-08 09:14:19


5
43860
OFFLINE
2023-01-08 18:14:19


6
43860
AVAILABLE
2023-01-08 22:14:19


7
43857
OFFLINE
2023-01-08 23:14:19




I track multiple id's and the it's current status. My goal is to know the offline time of each ID within 24 hours.
For example:

id -> 43857 on 2023-01-07 it would be an offline time of 5 hours.
id -> 43860 on 2023-01-08 it would be an offline time of 4 hours.


The calculation should be only if status is "OFFLINE" until next "AVAILABLE" status.
Currently I do this via python, I select affected rows within 24 hours, calculate and save the offline time in another table.
Is there a better way to calculate this? For example in SQL language?
I'm also open to new ideas, better ways to save the data in MySQL to make the calculation easier.
Btw.:
I also found this question which is pretty much what I need, but can someone explain me that query a little bit?

A: This would eliminate all time stamps that have no available status reached
WITH CTE as (SELECT
t1.id, t1.`timestamp`
  , (SELECT `timestamp` FROM tab1 t2 WHERE  t2.id = t1.id 
  AND t2.`status`= 'AVAILABLE'  
  AND t1.`timestamp` < t2.`timestamp` 
  ORDER BY t2.`idpk` ASC LIMIT 1) atime
FROM tab1 t1 
WHERE t1.`status`  = 'OFFLINE'
)
SELECT 
  id,
CONCAT(
TIMESTAMPDIFF(day,`timestamp`,atime) , ' days ',
MOD( TIMESTAMPDIFF(hour,`timestamp`,atime), 24), ' hours ',
MOD( TIMESTAMPDIFF(minute,`timestamp`,atime), 60), ' minutes '
, ' OFFLINE') `offline`
  FROM CTE
WHERE atime IS NOT NULL





id
offline




43857
0 days 5 hours 0 minutes  OFFLINE


43860
0 days 4 hours 0 minutes  OFFLINE




fiddle
