2

I have the following table in PostgreSQL 9.4, which persists users log on/off events (log on is event_type 1, log off is event_type 0).

CREATE TABLE user_online_offline_events (
  id serial,
  user_id int4,
  event_type int4,
  created_at timestamp
);

Sample data:

INSERT INTO user_online_offline_events
       (id, user_id, event_type, created_at)
VALUES (1,  123,     1,         '2015-10-07 12:15:00'),
       (2,  123,     0,         '2015-10-07 12:25:00'),
       (3,  123,     1,         '2015-10-07 12:45:00'),
       (4,  123,     0,         '2015-10-07 13:10:00');

I'd like to calculate the number of minutes each user was logged-on per hour:

| id | user_id |     time | minutes logged on |
+----+---------+----------+-------------------+
|  1 |     123 | 12:00:00 |                30 |
|  2 |     123 | 13:00:00 |                10 |

This is my WIP version. It's not really elegant, it still has the hour hard coded in, ignores user_ids and assumes online-offline events are consecutive:

select time, sum(minutes) / 60 as minutes from (
  SELECT
    date_trunc('hour', time) as time,
    CASE
    WHEN event_type = 0 AND lag(event_type, 1) OVER w = 1
      THEN
        extract(EPOCH FROM time - lag(time, 1) OVER w)
    WHEN event_type = 0 AND lag(event_type, 1) OVER w ISNULL
      THEN
        extract(EPOCH FROM time - date_trunc('hour', created_at))
    WHEN event_type = 1 AND lead(event_type, 1) OVER w ISNULL
      THEN
        extract(EPOCH FROM date_trunc('hour', time) + INTERVAL '1 hour' - time)
    ELSE 0
    END AS minutes
  FROM user_online_offline_events
  WHERE date_trunc('hour', time) = '2015-10-07 12:00:00'
  WINDOW w AS ( ORDER BY time )
  ORDER BY time
) m group by time;

How to do this properly?

  • Did you try something? The harder part is probably finding the corresponding logon-logoff pairs. – dezso Oct 7 '15 at 12:29
  • @dezso i've added my WIP version – Asaf David Oct 7 '15 at 12:35
  • 1
    It's instrumental to provide an actual table definition showing data types and constraints with such a question. The original CREATE TABLE script or what you get with \d user_online_offline_events in psql. And always your version of Postgres, please. Plus: define how to handle unmatched logging events: log-on without log-off and vice versa. And times never wrap around? – Erwin Brandstetter Oct 7 '15 at 12:56
  • Do you want to lump multiple days into one result: I.e.: sum all minutes online between 12:00 and 13:00 for all days in the table etc.? And do you want 24 rows (one fore each hour of the day) for ever user, even without online time? Is there a dedicated user table holding all relevant user_id distinctly? – Erwin Brandstetter Oct 7 '15 at 13:27
1

This is more sophisticated than I first understood. One way to handle it is with generate-series(), range types and associated functions and operators:

SELECT user_id, hour, sum(upper(min_on) - lower(min_on)) AS minutes_on
FROM  (
   SELECT user_id, hour, u.range * h.range AS min_on
   FROM  (
      SELECT hour, tsrange(hour, hour + interval '1h') AS range
      FROM   generate_series('2015-10-07 00:00'::timestamp  -- defines range of interest
                           , '2015-10-08 03:00'::timestamp  -- cut off the rest
                           , interval '1h') hour
      ) h
   JOIN  (
      SELECT user_id, event_type
           , tsrange(created_at
                   , lead(created_at) OVER (PARTITION BY user_id ORDER BY created_at)
                    ) AS range
      FROM   user_online_offline_events
      -- add WHERE conditions to limit selection
      -- careful with cutting off leading "on" / trailing "off" events
      ) u ON u.event_type = 1 AND u.range && h.range
   ) sub
GROUP  BY  user_id, hour
ORDER  BY  user_id, hour;

SQL Fiddle with extended data.

Major points

  • Define the outer time frame in generate_series() once. The function generates one row per hour.

  • Minutes per hours of the day in multiple days are summed up - if the outer time frame spans multiple days.

  • Hours without any online time are not in the result. If you need that use LEFT JOIN instead of JOIN

  • Using the overlap operator && for ranges to identify matching hours and the intersection operator * to calculate actual overlaps.

  • Resulting times are given as interval. Since multiple days might be summed, the result can be more than one hour and a simple EXTRACT() would not capture that. You can extract minutes as integer number this way:

    EXTRACT('epoch' FROM sum(upper(min_on) - lower(min_on))) / 60 AS minutes_on
    
  • Basic timestamp ranges are computed naively with the window function lead(). This assumes that every log-on is followed by a log-off, which typically misses special cases:

    • Multiple log-on / log-off events for the same user in a row
    • No matching log-on / log-off

    You need to define exactly which corner cases can exist and how you want to handle them. There is a solution either way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.