Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a table that stores sessions. The columns are: ID, Start (Timestamp) and Stop (Timestamp, can be NULL).

It's for online radio. A listener connects to the stream, and a row is created. Once they stop listening, Stop is updated to the current time.

Currently, it's about 80K rows per day.

I'd like to process the sessions once a day by determining the max amount of listeners for each minute, and push that data into a different table: Timestamp, ListenerCount (UNSIGNED SMALL INT).

I'm not super concerned with speed, just with working out how I can process this data within MySQL. I'm assuming I use a stored procedure (not much experience there), loop over each minute between the MIN(Start) and Max(Stop), and do a COUNT() between x-minute and x-minute:59.

Does this sound viable? Is there a better way?

Thanks for your time.

share|improve this question
up vote 1 down vote accepted

Here's an idea you could build on to achieve this: create a simple table that contains only a list of integers from 1 to 24*60 (i.e. number of minutes in a day). Use this to generate the list of minutes in the day you're interested in. Then join that on your session data, grouping by "minute" to get a count of active sessions.

Probably better explained with an example:


-- Holds a list of integers
create table minutes (m int) engine innodb;
-- The session data
create table sessions (id int,
                       st timestamp,
                       en timestamp) engine innodb;

Generate the list of "minutes"

delimiter ;;
create procedure fill_minutes()
  declare i int default 0;
  while i < 60*24 do
    insert into minutes(m) values (i);
    set i = i + 1;
  end while;
delimiter ;

start transaction;
call fill_minutes();

Adapted from Generating a range of numbers in MySQL

To generate the minutes in a given day:

    date('2013-01-01 00:00:00') + interval m minute
2013-01-01 00:00:00
2013-01-01 00:01:00
2013-01-01 00:02:00
2013-01-01 00:03:00
2013-01-01 00:04:00
2013-01-01 00:05:00

A bit of fake data:

insert into sessions (id, st, en)
values (1, '2013-01-01 00:00:00', '2013-01-01 00:10:00')
,      (2, '2013-01-01 00:05:00', '2013-01-01 00:10:00')
,      (3, '2013-01-01 00:07:00', '2013-01-01 00:12:00');

Join the minute table with the session data

    dates.d, count(*)
    (select date('2013-01-01 00:00:00') + interval m minute d from minutes) dates
    join sessions s on ((dates.d >= and (dates.d < s.en))
group by dates.d
order by dates.d;
d                   count(*)
2013-01-01 00:00:00 1
2013-01-01 00:01:00 1
2013-01-01 00:02:00 1
2013-01-01 00:03:00 1
2013-01-01 00:04:00 1
2013-01-01 00:05:00 2
2013-01-01 00:06:00 2
2013-01-01 00:07:00 3
2013-01-01 00:08:00 3
2013-01-01 00:09:00 3
2013-01-01 00:10:00 1
2013-01-01 00:11:00 1


  • I don't know how to avoid that "list of minutes" table in MySQL. Doable in Oracle with a select ... from dual connect by level <= ... or similar.
  • You'll probably need to restrict the join on sessions more to avoid a full scan of that table (limiting the start date at least, start and end sounds better)
share|improve this answer
This has been a great help. Thanks. – Duncan Fairley Mar 3 '13 at 11:48

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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