MariaDB 10.3

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

CREATE TABLE `player_stats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `player_id` bigint(20) DEFAULT NULL,
  `player_name` varchar(255) DEFAULT NULL,
  `player_ip` varchar(128) DEFAULT NULL,
  `player_state` int(1) DEFAULT '0',
  `player_online_time` bigint(20) DEFAULT '0',
  `player_last_login` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `player_id` (`player_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

insert into `player_stats` (`id`, `player_id`, `player_name`, `player_ip`, `player_state`, `player_online_time`, `player_last_login`) values('1','1111','Player 1','127.0.0.1','1','420','2022-08-31 21:00:00');
insert into `player_stats` (`id`, `player_id`, `player_name`, `player_ip`, `player_state`, `player_online_time`, `player_last_login`) values('2','2222','Player 2','127.0.0.1','0','690','2022-08-31 19:56:29');
insert into `player_stats` (`id`, `player_id`, `player_name`, `player_ip`, `player_state`, `player_online_time`, `player_last_login`) values('3','3333','Player 3','127.0.0.1','0','120','2022-08-18 12:05:04');

CREATE TABLE `player_connect_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `player_id` bigint(20) DEFAULT NULL,
  `player_name` varchar(255) DEFAULT NULL,
  `state` varchar(128) DEFAULT NULL,
  `time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

insert into `player_connect_log` (`id`, `player_id`, `player_name`, `state`, `time`) values('1','1111','Player 1','Connected','2022-08-29 12:00:00');
insert into `player_connect_log` (`id`, `player_id`, `player_name`, `state`, `time`) values('2','2222','Player 2','Connected','2022-08-29 12:30:00');
insert into `player_connect_log` (`id`, `player_id`, `player_name`, `state`, `time`) values('3','1111','Player 1','Disconnected','2022-08-29 13:00:00');
insert into `player_connect_log` (`id`, `player_id`, `player_name`, `state`, `time`) values('4','2222','Player 2','Disconnected','2022-08-29 13:30:00');
insert into `player_connect_log` (`id`, `player_id`, `player_name`, `state`, `time`) values('5','1111','Player 1','Connected','2022-08-30 10:00:00');
insert into `player_connect_log` (`id`, `player_id`, `player_name`, `state`, `time`) values('6','2222','Player 2','Connected','2022-08-30 11:30:00');
insert into `player_connect_log` (`id`, `player_id`, `player_name`, `state`, `time`) values('7','3333','Player 3','Connected','2022-08-30 12:00:00');
insert into `player_connect_log` (`id`, `player_id`, `player_name`, `state`, `time`) values('8','1111','Player 1','Disconnected','2022-08-30 13:30:00');
insert into `player_connect_log` (`id`, `player_id`, `player_name`, `state`, `time`) values('9','3333','Player 3','Disconnected','2022-08-30 14:00:00');
insert into `player_connect_log` (`id`, `player_id`, `player_name`, `state`, `time`) values('10','2222','Player 2','Disconnected','2022-08-30 20:00:00');
insert into `player_connect_log` (`id`, `player_id`, `player_name`, `state`, `time`) values('11','1111','Player 1','Connected','2022-08-31 12:00:00');
insert into `player_connect_log` (`id`, `player_id`, `player_name`, `state`, `time`) values('12','2222','Player 2','Connected','2022-08-31 13:00:00');
insert into `player_connect_log` (`id`, `player_id`, `player_name`, `state`, `time`) values('13','1111','Player 1','Disconnected','2022-08-31 13:30:00');
insert into `player_connect_log` (`id`, `player_id`, `player_name`, `state`, `time`) values('14','2222','Player 2','Disconnected','2022-08-31 15:00:00');
insert into `player_connect_log` (`id`, `player_id`, `player_name`, `state`, `time`) values('15','1111','Player 1','Connected','2022-08-31 21:00:00');
insert into `player_connect_log` (`id`, `player_id`, `player_name`, `state`, `time`) values('16','1111','Player 1','Disconnected','2022-08-31 22:00:00');
insert into `player_connect_log` (`id`, `player_id`, `player_name`, `state`, `time`) values('17','1111','Player 1','Connected','2022-09-02 10:00:00');

SELECT * FROM player_stats;
SELECT * FROM player_connect_log;


id
player_id
player_name
player_ip
player_state
player_online_time
player_last_login


1
1111
Player 1
127.0.0.1
1
420
2022-08-31 21:00:00

2
2222
Player 2
127.0.0.1
0
690
2022-08-31 19:56:29

3
3333
Player 3
127.0.0.1
0
120
2022-08-18 12:05:04




id
player_id
player_name
state
time


1
1111
Player 1
Connected
2022-08-29 12:00:00

2
2222
Player 2
Connected
2022-08-29 12:30:00

3
1111
Player 1
Disconnected
2022-08-29 13:00:00

4
2222
Player 2
Disconnected
2022-08-29 13:30:00

5
1111
Player 1
Connected
2022-08-30 10:00:00

6
2222
Player 2
Connected
2022-08-30 11:30:00

7
3333
Player 3
Connected
2022-08-30 12:00:00

8
1111
Player 1
Disconnected
2022-08-30 13:30:00

9
3333
Player 3
Disconnected
2022-08-30 14:00:00

10
2222
Player 2
Disconnected
2022-08-30 20:00:00

11
1111
Player 1
Connected
2022-08-31 12:00:00

12
2222
Player 2
Connected
2022-08-31 13:00:00

13
1111
Player 1
Disconnected
2022-08-31 13:30:00

14
2222
Player 2
Disconnected
2022-08-31 15:00:00

15
1111
Player 1
Connected
2022-08-31 21:00:00

16
1111
Player 1
Disconnected
2022-08-31 22:00:00

17
1111
Player 1
Connected
2022-09-02 10:00:00


/* retuen all players online time each session*/
SELECT 
  DATE(t1.time) AS date,
  TIME(t1.time) AS dc,
  t1.player_id,
  t1.player_name,
  COALESCE(
    TIMESTAMPDIFF(MINUTE, t2.time, t1.time),
    0
  ) time_online 
FROM
  player_connect_log t1 
  INNER JOIN player_connect_log t2 
    ON t1.player_id = t2.player_id 
    AND t1.time > t2.time 
    AND t1.state = 'Disconnected' 
WHERE NOT EXISTS 
  (SELECT 
    NULL 
  FROM
    player_connect_log t3 
  WHERE t1.player_id = t3.player_id 
    AND t1.time > t3.time 
    AND t3.time > t2.time)
ORDER BY t1.time;


date
dc
player_id
player_name
time_online


2022-08-29
13:00:00
1111
Player 1
60

2022-08-29
13:30:00
2222
Player 2
60

2022-08-30
13:30:00
1111
Player 1
210

2022-08-30
14:00:00
3333
Player 3
120

2022-08-30
20:00:00
2222
Player 2
510

2022-08-31
13:30:00
1111
Player 1
90

2022-08-31
15:00:00
2222
Player 2
120

2022-08-31
22:00:00
1111
Player 1
60


/* Returns given player online session and time each day. */
SELECT 
  DATE(t1.time),
  t1.player_id,
  t1.player_name,
  COALESCE(
    TIMESTAMPDIFF(MINUTE, t2.time, t1.time),
    0
  ) time_online 
FROM
  player_connect_log t1 
  INNER JOIN player_connect_log t2 
    ON t1.player_id = t2.player_id 
    AND t1.time > t2.time 
    AND t1.state = 'Disconnected' 
WHERE NOT EXISTS 
  (SELECT 
    NULL 
  FROM
    player_connect_log t3 
  WHERE t1.player_id = t3.player_id 
    AND t1.time > t3.time 
    AND t3.time > t2.time) 
    AND t1.player_id = '1111' 
ORDER BY t1.time;


DATE(t1.time)
player_id
player_name
time_online


2022-08-29
1111
Player 1
60

2022-08-30
1111
Player 1
210

2022-08-31
1111
Player 1
90

2022-08-31
1111
Player 1
60


/* returns all players connect and dissconnect time between to datetimes*/
SELECT player_name, state, time FROM player_connect_log WHERE time BETWEEN '2022-08-29 00:00:00' AND '2022-08-31 00:00:00' ORDER BY time;


player_name
state
time


Player 1
Connected
2022-08-29 12:00:00

Player 2
Connected
2022-08-29 12:30:00

Player 1
Disconnected
2022-08-29 13:00:00

Player 2
Disconnected
2022-08-29 13:30:00

Player 1
Connected
2022-08-30 10:00:00

Player 2
Connected
2022-08-30 11:30:00

Player 3
Connected
2022-08-30 12:00:00

Player 1
Disconnected
2022-08-30 13:30:00

Player 3
Disconnected
2022-08-30 14:00:00

Player 2
Disconnected
2022-08-30 20:00:00


/* Returns a table with all players online at a given time. */
SELECT 
  player_name 
FROM
  (SELECT 
    player_name,
    TIME AS intime,
    (SELECT 
      MIN(TIME) 
    FROM
      player_connect_log t2 
    WHERE t2.player_name = t3.player_name 
      AND t2.time > t3.time 
      AND t2.state = 'Disconnected') AS outtime 
  FROM
    player_connect_log t3 
  WHERE t3.state = 'Connected') t1 
WHERE '2022-08-30 12:45:00' BETWEEN t1.intime 
  AND t1.outtime ;


player_name


Player 1

Player 2

Player 3

`id`	`player_id`	`player_name`	`player_ip`	`player_state`	`player_online_time`	`player_last_login`
`1`	`1111`	`Player 1`	`127.0.0.1`	`1`	`420`	`2022-08-31 21:00:00`
`2`	`2222`	`Player 2`	`127.0.0.1`	`0`	`690`	`2022-08-31 19:56:29`
`3`	`3333`	`Player 3`	`127.0.0.1`	`0`	`120`	`2022-08-18 12:05:04`

`id`	`player_id`	`player_name`	`state`	`time`
`1`	`1111`	`Player 1`	`Connected`	`2022-08-29 12:00:00`
`2`	`2222`	`Player 2`	`Connected`	`2022-08-29 12:30:00`
`3`	`1111`	`Player 1`	`Disconnected`	`2022-08-29 13:00:00`
`4`	`2222`	`Player 2`	`Disconnected`	`2022-08-29 13:30:00`
`5`	`1111`	`Player 1`	`Connected`	`2022-08-30 10:00:00`
`6`	`2222`	`Player 2`	`Connected`	`2022-08-30 11:30:00`
`7`	`3333`	`Player 3`	`Connected`	`2022-08-30 12:00:00`
`8`	`1111`	`Player 1`	`Disconnected`	`2022-08-30 13:30:00`
`9`	`3333`	`Player 3`	`Disconnected`	`2022-08-30 14:00:00`
`10`	`2222`	`Player 2`	`Disconnected`	`2022-08-30 20:00:00`
`11`	`1111`	`Player 1`	`Connected`	`2022-08-31 12:00:00`
`12`	`2222`	`Player 2`	`Connected`	`2022-08-31 13:00:00`
`13`	`1111`	`Player 1`	`Disconnected`	`2022-08-31 13:30:00`
`14`	`2222`	`Player 2`	`Disconnected`	`2022-08-31 15:00:00`
`15`	`1111`	`Player 1`	`Connected`	`2022-08-31 21:00:00`
`16`	`1111`	`Player 1`	`Disconnected`	`2022-08-31 22:00:00`
`17`	`1111`	`Player 1`	`Connected`	`2022-09-02 10:00:00`

`date`	`dc`	`player_id`	`player_name`	`time_online`
`2022-08-29`	`13:00:00`	`1111`	`Player 1`	`60`
`2022-08-29`	`13:30:00`	`2222`	`Player 2`	`60`
`2022-08-30`	`13:30:00`	`1111`	`Player 1`	`210`
`2022-08-30`	`14:00:00`	`3333`	`Player 3`	`120`
`2022-08-30`	`20:00:00`	`2222`	`Player 2`	`510`
`2022-08-31`	`13:30:00`	`1111`	`Player 1`	`90`
`2022-08-31`	`15:00:00`	`2222`	`Player 2`	`120`
`2022-08-31`	`22:00:00`	`1111`	`Player 1`	`60`

`DATE(t1.time)`	`player_id`	`player_name`	`time_online`
`2022-08-29`	`1111`	`Player 1`	`60`
`2022-08-30`	`1111`	`Player 1`	`210`
`2022-08-31`	`1111`	`Player 1`	`90`
`2022-08-31`	`1111`	`Player 1`	`60`

`player_name`	`state`	`time`
`Player 1`	`Connected`	`2022-08-29 12:00:00`
`Player 2`	`Connected`	`2022-08-29 12:30:00`
`Player 1`	`Disconnected`	`2022-08-29 13:00:00`
`Player 2`	`Disconnected`	`2022-08-29 13:30:00`
`Player 1`	`Connected`	`2022-08-30 10:00:00`
`Player 2`	`Connected`	`2022-08-30 11:30:00`
`Player 3`	`Connected`	`2022-08-30 12:00:00`
`Player 1`	`Disconnected`	`2022-08-30 13:30:00`
`Player 3`	`Disconnected`	`2022-08-30 14:00:00`
`Player 2`	`Disconnected`	`2022-08-30 20:00:00`