Postgres 12

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

create table test(
  Username text,
  Date timestamp,
  Event text
);

insert into test values
('Luis', to_timestamp('30/08/2022 1:00am', 'DD/MM/YYYY HH12:MIam'), 'in'),
('Maria', to_timestamp('30/08/2022 1:05am', 'DD/MM/YYYY HH12:MIam'), 'in'),
('Luis', to_timestamp('30/08/2022 1:30am', 'DD/MM/YYYY HH12:MIam'), 'out'),
('Jorge', to_timestamp('30/08/2022 1:50am', 'DD/MM/YYYY HH12:MIam'), 'in'),
('Carla', to_timestamp('30/08/2022 3:40am', 'DD/MM/YYYY HH12:MIam'), 'in'),
('Carla', to_timestamp('30/08/2022 4:30am', 'DD/MM/YYYY HH12:MIam'), 'out'),
('Maria', to_timestamp('30/08/2022 6:10am', 'DD/MM/YYYY HH12:MIam'), 'out'),
('Luis', to_timestamp('30/08/2022 7:00am', 'DD/MM/YYYY HH12:MIam'), 'in'),
('Luis', to_timestamp('30/08/2022 8:00am', 'DD/MM/YYYY HH12:MIam'), 'out');

select * from test;
9 rows affected



username
date
event


Luis
2022-08-30 01:00:00
in

Maria
2022-08-30 01:05:00
in

Luis
2022-08-30 01:30:00
out

Jorge
2022-08-30 01:50:00
in

Carla
2022-08-30 03:40:00
in

Carla
2022-08-30 04:30:00
out

Maria
2022-08-30 06:10:00
out

Luis
2022-08-30 07:00:00
in

Luis
2022-08-30 08:00:00
out


Select 
  Username, 
  Date as DateStartVPN, 
  (select min(Date) 
  from test 
  where Username=t.Username 
  and date > t.date
  and Event='out') DateFinishVPN
from test t where Event = 'in';


username
datestartvpn
datefinishvpn


Luis
2022-08-30 01:00:00
2022-08-30 01:30:00

Maria
2022-08-30 01:05:00
2022-08-30 06:10:00

Jorge
2022-08-30 01:50:00
null

Carla
2022-08-30 03:40:00
2022-08-30 04:30:00

Luis
2022-08-30 07:00:00
2022-08-30 08:00:00


select 
  Username as "User", 
  EventDate as "DateStartVpn", 
  NextEventDate as "DateFinishVpn" 
from (
select
  Username,
  Date EventDate,
  Event,
  lead(event) over(partition by Username order by Date) as NextEvent,
  lead(Date) over(partition by Username order by Date) as NextEventDate
from test
) t where t.event != 'out'
order by EventDate, User;


User
DateStartVpn
DateFinishVpn


Luis
2022-08-30 01:00:00
2022-08-30 01:30:00

Maria
2022-08-30 01:05:00
2022-08-30 06:10:00

Jorge
2022-08-30 01:50:00
null

Carla
2022-08-30 03:40:00
2022-08-30 04:30:00

Luis
2022-08-30 07:00:00
2022-08-30 08:00:00

username	date	event
Luis	2022-08-30 01:00:00	in
Maria	2022-08-30 01:05:00	in
Luis	2022-08-30 01:30:00	out
Jorge	2022-08-30 01:50:00	in
Carla	2022-08-30 03:40:00	in
Carla	2022-08-30 04:30:00	out
Maria	2022-08-30 06:10:00	out
Luis	2022-08-30 07:00:00	in
Luis	2022-08-30 08:00:00	out