MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE IF NOT EXISTS `logging` (
  `id` int(6) unsigned NOT NULL,
  `status` varchar(150) NOT NULL,
  `timestamp` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `logging` (`id`, `status`, `timestamp`) VALUES
  ('1', 'logout', '2021-01-01 05:01:00'),
  ('2', 'login', '2021-01-01 06:02:00'),
  ('3', 'online', '2021-01-01 06:03:00'),
  ('4', 'away', '2021-01-01 06:04:00'),
  ('5', 'online', '2021-01-01 06:05:00'),
  ('6', 'logout', '2021-01-02 04:00:00'),
  ('7', 'login', '2021-01-02 04:05:00'),
  ('8', 'online', '2021-01-02 04:07:00'),
  ('9', 'away', '2021-01-02 04:08:00'),
  ('10', 'break', '2021-01-02 04:10:00'),
  ('11', 'online', '2021-01-02 04:15:00'),
  ('12', 'logout', '2021-01-02 04:55:00'),
  ('13', 'login', '2021-01-02 05:04:00'),
  ('14', 'online', '2021-01-02 05:05:00'),
  ('15', 'away', '2021-01-03 05:01:00'),
  ('16', 'logout', '2021-01-03 05:02:00'),
  ('17', 'login', '2021-01-03 05:04:00'),
  ('18', 'online', '2021-01-03 05:05:00'),
  ('19', 'logout', '2021-01-04 03:05:00');
SELECT * FROM logging;


id
status
timestamp


1
logout
2021-01-01 05:01:00

2
login
2021-01-01 06:02:00

3
online
2021-01-01 06:03:00

4
away
2021-01-01 06:04:00

5
online
2021-01-01 06:05:00

6
logout
2021-01-02 04:00:00

7
login
2021-01-02 04:05:00

8
online
2021-01-02 04:07:00

9
away
2021-01-02 04:08:00

10
break
2021-01-02 04:10:00

11
online
2021-01-02 04:15:00

12
logout
2021-01-02 04:55:00

13
login
2021-01-02 05:04:00

14
online
2021-01-02 05:05:00

15
away
2021-01-03 05:01:00

16
logout
2021-01-03 05:02:00

17
login
2021-01-03 05:04:00

18
online
2021-01-03 05:05:00

19
logout
2021-01-04 03:05:00


-- view sessions
SELECT * 
FROM logging
WHERE status IN ('login', 'online', 'logout');


id
status
timestamp


1
logout
2021-01-01 05:01:00

2
login
2021-01-01 06:02:00

3
online
2021-01-01 06:03:00

5
online
2021-01-01 06:05:00

6
logout
2021-01-02 04:00:00

7
login
2021-01-02 04:05:00

8
online
2021-01-02 04:07:00

11
online
2021-01-02 04:15:00

12
logout
2021-01-02 04:55:00

13
login
2021-01-02 05:04:00

14
online
2021-01-02 05:05:00

16
logout
2021-01-03 05:02:00

17
login
2021-01-03 05:04:00

18
online
2021-01-03 05:05:00

19
logout
2021-01-04 03:05:00


/*
i want to have an output:

date	A	B
2021-01-01	2021-01-02 04:07:00	2021-01-02 04:55:00
2021-01-02	2021-01-02 05:05:00	2021-01-03 04:59:59
2021-01-03	2021-01-03 05:05:00	2021-01-04 03:05:00

*/
-- old query
SELECT t1.`timestamp` login, t2.`timestamp` online, 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'
  AND NOT EXISTS ( SELECT NULL
                   FROM logging t4
                   WHERE t1.`timestamp` < t4.`timestamp`
                     AND t4.`timestamp` < t2.`timestamp`
                     AND t4.status IN ('login', 'online', 'logout') )
  AND NOT EXISTS ( SELECT NULL
                   FROM logging t5
                   WHERE t2.`timestamp` < t5.`timestamp`
                     AND t5.`timestamp` < t3.`timestamp`
                     AND t5.status IN ('login', 'logout') )
  AND DATE(t1.`timestamp` - INTERVAL 5 HOUR) = DATE(t3.`timestamp` - INTERVAL '04:59:59' HOUR_SECOND);


login
online
logout


2021-01-01 06:02:00
2021-01-01 06:03:00
2021-01-02 04:00:00

2021-01-02 04:05:00
2021-01-02 04:07:00
2021-01-02 04:55:00

2021-01-03 05:04:00
2021-01-03 05:05:00
2021-01-04 03:05:00


-- remove date border crossing
-- select only last session data
SELECT DATE(t1.`timestamp` - INTERVAL 5 HOUR) `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'
  AND NOT EXISTS ( SELECT NULL
                   FROM logging t4
                   WHERE t1.`timestamp` < t4.`timestamp`
                     AND t4.`timestamp` < t2.`timestamp`
                     AND t4.status IN ('login', 'online', 'logout') )
  AND NOT EXISTS ( SELECT NULL
                   FROM logging t5
                   WHERE t2.`timestamp` < t5.`timestamp`
                     AND t5.`timestamp` < t3.`timestamp`
                     AND t5.status IN ('login', 'logout') )
GROUP BY `date`


date
login
online
logout


2021-01-01
2021-01-02 04:05:00
2021-01-02 04:07:00
2021-01-02 04:55:00

2021-01-02
2021-01-02 05:04:00
2021-01-02 05:05:00
2021-01-03 05:02:00

2021-01-03
2021-01-03 05:04:00
2021-01-03 05:05:00
2021-01-04 03:05:00


-- The same (in cte) + normalize logout datetime (in outer query)
WITH cte AS (
SELECT DATE(t1.`timestamp` - INTERVAL 5 HOUR) `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'
  AND NOT EXISTS ( SELECT NULL
                   FROM logging t4
                   WHERE t1.`timestamp` < t4.`timestamp`
                     AND t4.`timestamp` < t2.`timestamp`
                     AND t4.status IN ('login', 'online', 'logout') )
  AND NOT EXISTS ( SELECT NULL
                   FROM logging t5
                   WHERE t2.`timestamp` < t5.`timestamp`
                     AND t5.`timestamp` < t3.`timestamp`
                     AND t5.status IN ('login', 'logout') )
GROUP BY `date`
)
SELECT `date`,
       online,
       CASE WHEN DATE(online - INTERVAL 5 HOUR) = DATE(logout - INTERVAL '04:59:59' HOUR_SECOND)
            THEN logout
            ELSE DATE(online + INTERVAL 19 HOUR) + INTERVAL '04:59:59' HOUR_SECOND
            END logout
FROM cte


date
online
logout


2021-01-01
2021-01-02 04:07:00
2021-01-02 04:55:00

2021-01-02
2021-01-02 05:05:00
2021-01-03 04:59:59

2021-01-03
2021-01-03 05:05:00
2021-01-04 03:05:00

id	status	timestamp
1	logout	2021-01-01 05:01:00
2	login	2021-01-01 06:02:00
3	online	2021-01-01 06:03:00
4	away	2021-01-01 06:04:00
5	online	2021-01-01 06:05:00
6	logout	2021-01-02 04:00:00
7	login	2021-01-02 04:05:00
8	online	2021-01-02 04:07:00
9	away	2021-01-02 04:08:00
10	break	2021-01-02 04:10:00
11	online	2021-01-02 04:15:00
12	logout	2021-01-02 04:55:00
13	login	2021-01-02 05:04:00
14	online	2021-01-02 05:05:00
15	away	2021-01-03 05:01:00
16	logout	2021-01-03 05:02:00
17	login	2021-01-03 05:04:00
18	online	2021-01-03 05:05:00
19	logout	2021-01-04 03:05:00

date	login	online	logout
2021-01-01	2021-01-02 04:05:00	2021-01-02 04:07:00	2021-01-02 04:55:00
2021-01-02	2021-01-02 05:04:00	2021-01-02 05:05:00	2021-01-03 05:02:00
2021-01-03	2021-01-03 05:04:00	2021-01-03 05:05:00	2021-01-04 03:05:00