Calculate maximum number of concurrent user sessions

Question

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

Please provide your version of Postgres and the exact table definition showing data types and constraints (`CREATE TABLE` statement). And it's unclear whether all sessions must overlap together to be counted: session 1 could overlap with session 2 for 30 min which, in turn, overlaps with session 3 - but session 1 and session 3 still do not overlap (long enough). We need an *exact* definition of the task. Is it about the 30-min period with the most uninterrupted concurrent sessions (per day)? — Erwin Brandstetter, Dec 18 '19 at 01:49
@ErwinBrandstetter All sessions must overlap together for at least 30mins to be counted, In your example, the result will still be 2. Our application uses a concurrent license model, so at the end of the month, I'll be using this query to find the peak usage and bill accordingly. The postgres version is 9.6 and I've edited the question to add the table definition and some clarifications. — Athul Dilip, Dec 18 '19 at 08:50
`EndTime timestamp NULL,` Using NOT NULL and +Inf as default value normally results in simpler queries. — wildplasser, Dec 18 '19 at 12:11
@wildplasser: Good advice! Makes queries simpler and uses simple indexes much more readily. — Erwin Brandstetter, Dec 18 '19 at 12:22

Erwin Brandstetter · Accepted Answer · 2019-12-18T23:05:24.707

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:

Ignoring time zones altogether in Rails and PostgreSQL

Calculate maximum number of concurrent user sessions

1 Answers1

Linked