Finding total session time of a user in postgres

Question

I am trying to create a query that will give me a column of total time logged in for each month for each user.

username | auth_event_type |         time        | credential_id 

Joe      |       1         | 2021-11-01 09:00:00 | 44
Joe      |       2         | 2021-11-01 10:00:00 | 44
Jeff     |       1         | 2021-11-01 11:00:00 | 45
Jeff     |       2         | 2021-11-01 12:00:00 | 45
Joe      |       1         | 2021-11-01 12:00:00 | 46    
Joe      |       2         | 2021-11-01 12:30:00 | 46
Joe      |       1         | 2021-12-06 14:30:00 | 47
Joe      |       2         | 2021-12-06 15:30:00 | 47

The auth_event_type column specifies whether the event was a login (1) or logout (2) and the credential_id indicates the session.

I'm trying to create a query that would have an output like this:

username | year_month | total_time
Joe      | 2021-11    | 1:30
Jeff     | 2021-11    | 1:00
Joe      | 2021-12    | 1:00

How would I go about doing this in postgres? I am thinking it would involve a window function? If someone could point me in the right direction that would be great. Thank you.

Answer 1

Solution 1 partially working

Not sure that window functions will help you in your case, but aggregate functions will :

WITH list AS
(
SELECT username
     , date_trunc('month', time) AS year_month
     , max(time ORDER BY time) - min(time ORDER BY time) AS session_duration
  FROM your_table
 GROUP BY username, date_trunc('month', time), credential_id
 )
SELECT username
     , to_char (year_month, 'YYYY-MM') AS year_month
     , sum(session_duration) AS total_time
  FROM list
 GROUP BY username, year_month

The first part of the query aggregates the login/logout times for the same username, credential_id, the second part makes the sum per year_month of the difference between the login/logout times. This query works well until the login time and logout time are in the same month, but it fails when they aren't.

Solution 2 fully working

In order to calculate the total_time per username and per month whatever the login time and logout time are, we can use a time range approach which intersects the session ranges [login_time, logout_time) with the monthly ranges [monthly_start_time, monthly_end_time) :

WITH monthly_range AS
(
SELECT to_char(m.month_start_date, 'YYYY-MM') AS month
     , tsrange(m.month_start_date, m.month_start_date+ interval '1 month' ) AS monthly_range
  FROM
     ( SELECT generate_series(min(date_trunc('month', time)), max(date_trunc('month', time)), '1 month') AS month_start_date
         FROM your_table
     ) AS m
), session_range AS
(
SELECT username
     , tsrange(min(time ORDER BY auth_event_type), max(time ORDER BY auth_event_type)) AS session_range
  FROM your_table
 GROUP BY username, credential_id
)
SELECT s.username
     , m.month
     , sum(upper(p.period) - lower(p.period)) AS total_time
  FROM monthly_range AS m
 INNER JOIN session_range AS s
    ON s.session_range && m.monthly_range
 CROSS JOIN LATERAL (SELECT s.session_range * m.monthly_range AS period) AS p
 GROUP BY s.username, m.month

see the result in dbfiddle