Mysql Select After Row On Specific Rules

I have a table like this: CREATE TABLE IF NOT EXISTS `logging` ( `id` int(6) unsigned NOT NULL, `status` varchar(150) NOT NULL, `timestamp` DATETIME NOT NULL, PRIMARY KEY (

Solution 1:

Check this:

WITH cte AS (
SELECTDATE(t1.`timestamp` -INTERVAL5HOUR) `date`,
       MAX(t1.`timestamp`) login, 
       MAX(t2.`timestamp`) online, 
       MAX(t3.`timestamp`) logout
FROM logging t1
JOIN logging t2 ON t1.`timestamp` < t2.`timestamp`
JOIN logging t3 ON t2.`timestamp` < t3.`timestamp`
WHERE t1.status ='login'AND t2.status ='online'AND t3.status ='logout'ANDNOTEXISTS ( SELECTNULLFROM logging t4
                   WHERE t1.`timestamp` < t4.`timestamp`
                     AND t4.`timestamp` < t2.`timestamp`
                     AND t4.status IN ('login', 'online', 'logout') )
  ANDNOTEXISTS ( SELECTNULLFROM logging t5
                   WHERE t2.`timestamp` < t5.`timestamp`
                     AND t5.`timestamp` < t3.`timestamp`
                     AND t5.status IN ('login', 'logout') )
GROUPBY `date`
)
SELECT `date`,
       online,
       CASEWHENDATE(online -INTERVAL5HOUR) =DATE(logout -INTERVAL'04:59:59' HOUR_SECOND)
            THEN logout
            ELSEDATE(online +INTERVAL19HOUR) +INTERVAL'04:59:59' HOUR_SECOND
            END logout
FROM cte

fiddle

CTE is used for visibility only - you may combine everything into one query.