Count records with start and end fields that span a given value

Question

Suppose I have a table (MySQL):

CREATE TABLE sessions (
    session_id INT NOT NULL AUTO_INCREMENT,
    name CHAR(12),
    start INT,
    end INT,
    PRIMARY KEY (session_id)
)

to track users who are logged in to the app. Each user login creates an entry in this table, setting the start time (as an integer number of seconds in seconds from the Unix era), and logout updates this table in the same way as the end time. My problem is to find the number of registered users at five minute intervals, for a range of time (usually per day).

What I have done so far is to write a procedure that traverses the data.

SET t = begin_time;
WHILE t <= end_time DO
    SELECT t, COUNT(1) FROM TABLE WHERE start <= t AND end >= t;
    SET t = t + 300;
END WHILE;

It takes a lot of time; I am looking for alternative solutions to this problem. Web links, pointers - any help will do.

Thanks in advance.

Answer 1

, .

:

SET begin_time = ...
SET end_time = ...
SET interval_sec = 300;

CREATE TEMPORARY TABLE timestamps (unixtime int);
SET t = (begin_time + interval_sec - 1) div interval_sec;
WHILE t <= end_time DO
  INSERT INTO timestamps (unixtime) VALUES (t);
  SET t = t + interval_sec;
END WHILE;

SELECT
  t.unixtime,
  COUNT(s.session_id)
FROM timestamps t
  LEFT JOIN sessions s ON t.unixtime >= s.start AND t.unixtime < s.end;
GROUP BY t.unixtime

, , , 5- . , , SET t = begin_time;.

. , , , COUNT(s.session_id) COUNT(DISTINCT s.name).

Answer 2

SELECT (end-start)-(end-start)%300, COUNT(1) FROM session GROUP BY 1 ORDER BY 1;