CODEWITHSUNDEEP
sqlpostgresqloverlapgaps-and-islandspostgresql-9.6
Athul Dilip
Athul Dilip

Reputation: 183

Calculate maximum number of concurrent user sessions

I have a UserSession table in PostgreSQL 9.6 that stores user's login and logout time, I want to calculate the maximum number of concurrent sessions - which are only considered to be concurrent if they overlap for at least 30 minutes.

Example

userid      |  starttime                |  endtime  
------------+---------------------------+--------------------------
1           |  01-Oct-19 6:00:00 AM     |    01-Oct-19 11:10:00 AM  
2           |  01-Oct-19 11:00:00 AM    |    01-Oct-19 4:00:00 PM 
3           |  01-Oct-19 10:30:00 AM    |    01-Oct-19 4:00:00 PM

Here, session 1 and 2 are not concurrent since the they only overlap for 10 mins and session 1 and 3 are concurrent since they overlap for more than 30 mins, So the result is 2 concurrent sessions.

NOTE: Result will only be n if all n sessions overlap for at least 30 mins.

Table Definition

CREATE TABLE UserSessions (
    SessionID bigserial NOT NULL,
    UserID bigint NOT NULL,
    StartTime timestamp NOT NULL,
    EndTime timestamp NULL,
    OrganisationID bigint NOT NULL,
    CONSTRAINT PK_SessionsID PRIMARY KEY(SessionID),
    CONSTRAINT FK_UserID FOREIGN KEY(UserID) REFERENCES GlobalUsers(UserID),
    CONSTRAINT FK_OrganisationID FOREIGN KEY(OrganisationID) REFERENCES Organisations(OrganisationID)
);

Similar Questions

There is a similar question here: Count max number of concurrent user session, but there concurrent means at the same point in time and in my case I need to check if they overlap for at least 30 mins

Upvotes: 2

Views: 1543

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658172

Deduct 30 minutes from the end (or start) of each time range. Then basically proceed as outlined in my referenced "simple" answer (adjusting for the 30 min in the right direction everywhere). Ranges shorter than 30 minutes are eliminated a priori - which makes sense as those can never be part of a 30 minutes period of continuous overlap. Also makes the query faster.

Calculating for all days in Oct 2019 (example range):

WITH range AS (SELECT timestamp '2019-10-01' AS start_ts  -- incl. lower bound
                    , timestamp '2019-11-01' AS end_ts)   -- excl. upper bound
, cte AS (
   SELECT userid, starttime
       -- default to current timestamp if NULL
        , COALESCE(endtime, localtimestamp) - interval '30 min' AS endtime
   FROM   usersessions, range r
   WHERE  starttime <  r.end_ts  -- count overlaps *starting* in outer time range
   AND   (endtime   >= r.start_ts + interval '30 min' OR endtime IS NULL)

   )
, ct AS (
   SELECT ts, sum(ct) OVER (ORDER BY ts, ct) AS session_ct
   FROM  (
      SELECT endtime AS ts, -1 AS ct FROM cte
      UNION ALL
      SELECT starttime    , +1       FROM cte
      ) sub
   )
SELECT ts::date, max(session_ct) AS max_concurrent_sessions
FROM   ct, range r
WHERE  ts >= r.start_ts
AND    ts <  r.end_ts            -- crop outer time range
GROUP  BY ts::date
ORDER  BY 1;

db<>fiddle here

Be aware that LOCALTIMESTAMP depends on the time zone of the current session. Consider using timestamptz in your table and CURRENT_TIMESTAMP instead. See:

Upvotes: 2

Related Questions