CODEWITHSUNDEEP
mysqldatetimejoinsql-timestamp
Dian Arief R
Dian Arief R

Reputation: 89

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 (`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'),
  ('20', 'login', '2021-01-04 05:07:00'),
  ('21', 'online', '2021-01-04 06:00:00'),
  ('22', 'logout', '2021-01-05 10:00:00'),
  ('23', 'login', '2021-01-05 11:00:00'),
  ('24', 'away', '2021-01-05 11:01:00'),
  ('25', 'online', '2021-01-06 06:01:00'),
  ('26', 'login', '2021-01-07 06:01:00'),
  ('26', 'logout', '2021-01-07 07:01: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 logout 2021-01-02 04:55:00

......

data provided on the insert query above.

i want to have an output:

date A (online) B (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
2021-01-04 2021-01-04 06:00:00 2021-01-04 04:59:59
2021-01-05 2021-01-04 05:00:00 2021-01-05 10:00:00
2021-01-06 2021-01-04 11:00:00 2021-01-06 04:59:59

the rule is, 1 log day is from 5:00:00 - (next day) 04:59:59. 'A' is timestamp start from the last online (after login, if any), 'B' is timestamp from the last logout (if there is no logout, B set to 04:59:59. Another rule is, when the last day doesnt have 'logout' after 'online', it's counted to the next log day (the next log day set to be 05.00.00 if the last record from the last log day is 'online')

currently i'm using this query to apply the log day rule:

SELECT date(t1.timestamp) dt, (t2.timestamp) A, (t3.timestamp) B, t1.status, t2.status, t3.status
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 loggingt4
               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 6 HOUR) = DATE(t3.timestamp - INTERVAL '05:59:59' HOUR_SECOND);

Upvotes: 0

Views: 77

Answers (1)

Akina
Akina

Reputation: 42632

Check this:

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

fiddle

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

Upvotes: 1

Related Questions