Order the log of my VPN, I have list of: username, date+hour, (in or out) to VPN

user_17399289 0
Viewed 37

Event: "in" connect VPN , "out" disconnect VPN

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;

| Username    |     Date           | Event |
------------------------------------------------
| Luis        | 30/08/2022 1:00am  |  in   |
| Maria       | 30/08/2022 1:05am  |  in   |
| Luis        | 30/08/2022 1:30am  |  out  |
| Jorge       | 30/08/2022 1:50am  |  in   |
| Carla       | 30/08/2022 3:40am  |  in   |
| Carla       | 30/08/2022 4:30am  |  out  |
| Maria       | 30/08/2022 6:10am  |  out  |
| Luis        | 30/08/2022 7:00am  |  in   |
| Luis        | 30/08/2022 8:00am  |  out  |

I try to get:

| User   |     DateStartVPN     |    DateFinishVPN   |
----
| Luis   |   30/08/2022 1:00am  |  30/08/2022 1:30am |
| Maria  |   30/08/2022 1:05am  |  30/08/2022 6:10am |
| Jorge  |   30/08/2022 1:50am  |  30/08/2022 5:30am |
| Carla  |   30/08/2022 3:40am  |  30/08/2022 4:30am |
| Luis   |   30/08/2022 7:00am  |  30/08/2022 8:00am |

I try something like

Select user,
       date as DateStartVPN,
       (select min(date) from table where date> date) DateFinishVPN as
from TABLE
where Event = 'in'

but I only get null

postgresql sql
1 Answers

Your original query is ok, but you need to reference your outer table:

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

While your original query works, I suggest you to look into window functions for this kind of tasks. With window functions, you can process groups of data without doing self-joins or using subqueries, which are not always optimal. E.g., with window functions something like this would work for you:

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

db<>fiddle here

user_190322060
Related