Triggers and MySQL.

Statistics with triggers.

This tutorial is a bit the same as the counters tutorial except that it deals with the time aspect as well.
So let's again create a table for the events and one for the counters but now with a timestamp:

CREATE TABLE Events(
	id bigint,
	eventtype ENUM('start','end'),
	eventtime datetime,
	PRIMARY KEY(id)
);

CREATE TABLE Event_counters(
	name varchar(255),
	eventtime datetime,
	value bigint
	);

So let's see what we want to know:
(Just some examples)
- the current call-count
- the maximum call-count
- the maximum call-count for every 5 minutes.
So what would it look like in a trigger:
The current call count:

IF (NEW.eventtype='start') THEN
	IF (select count(*) from Event_counters WHERE name='Current calls') > 0 THEN
	  UPDATE Event_counters SET value = value + 1 , eventtime = now() WHERE
      name='Current calls';
    ELSE
      INSERT INTO Event_counters values('Current calls',now(),1);
    END IF;
END IF;
IF (NEW.eventtype='end') THEN
	UPDATE Event_counters SET value = value - 1 WHERE name='Current calls';
END IF;

The maximum call count:

IF (NEW.eventtype='start') THEN
	IF (select count(*) from Event_counters WHERE name='Max calls') > 0 THEN
      IF (select value from Event_counters WHERE name='Max calls') <
      (select value from Event_counters WHERE name='Current calls') THEN
        UPDATE Event_counters SET value = value + 1 , eventtime = now()
        WHERE name='Max calls';
      END IF;
    ELSE
      INSERT INTO Event_counters values('Max calls',now(),1);
    END IF;
END IF;

The maximum call-count for every 5 minutes: Now this is a bit of a tricky one but with a bit of thinking we can solve this. So what we do is first convert the datetime into a timestamp(seconds since 01-01-1970 00:00:00) then divide by 300 and round down(= 5 minutes in seconds) and then multiplying again by 300. After we convert this back into a datetime it will have the value of the last 5 minute-period that started. So to round to 5 minutes it would be:
from_unixtime(floor((unix_timestamp(now())/300))*300) Also we need to take care that the maximum for the 5 minutes is updated when there is an 'end'-event and there has been no 'start'-event in that 5-minute-period.

IF (NEW.eventtype='start') THEN
	IF (select count(*) from Event_counters WHERE name='Max calls 5min' and eventtime =
	from_unixtime(floor(unix_timestamp(now())/300)*300)) > 0 THEN
      IF (select value from Event_counters WHERE name='Max calls 5min' and eventtime =
	  from_unixtime(floor(unix_timestamp(now())/300)*300)) <
	  (select value from Event_counters WHERE name='Current calls') THEN
        select value into @temp_counter from Event_counters WHERE name='Current calls';
        UPDATE Event_counters SET value = @temp_counter WHERE name='Max calls 5min' and eventtime =
		from_unixtime(floor((unix_timestamp(now())/300))*300);
      END IF;
    ELSE
        select value into @temp_counter from Event_counters WHERE name='Current calls';
        INSERT INTO Event_counters values ('Max calls 5min',
		from_unixtime(floor(unix_timestamp(now())/300)*300),@temp_counter);
    END IF;
END IF;

IF (NEW.eventtype='end') THEN
    IF (select count(*) from Event_counters WHERE name='Max calls 5min' and eventtime =
	from_unixtime(floor(unix_timestamp(now())/300)*300)) = 0 THEN
      select value into @temp_counter from Event_counters WHERE name='Current calls';
      INSERT INTO Event_counters values ('Max calls 5min',
	  from_unixtime(floor(unix_timestamp(now())/300)*300),@temp_counter);
    END IF;
  END IF;

You can do this for all counters and the total would look something like this:

DELIMITER |
CREATE TRIGGER after_insert
AFTER INSERT
ON Events
FOR EACH ROW BEGIN
  IF (NEW.eventtype='start') THEN
    IF (select count(*) from Event_counters WHERE name='Current calls') > 0 THEN
	  UPDATE Event_counters SET value = value + 1 , eventtime = now() WHERE
      name='Current calls';
    ELSE
      INSERT INTO Event_counters values('Current calls',now(),1);
    END IF;
    IF (select count(*) from Event_counters WHERE name='Max calls') > 0 THEN
      IF (select value from Event_counters WHERE name='Max calls') <
      (select value from Event_counters WHERE name='Current calls') THEN
        UPDATE Event_counters SET value = value + 1 , eventtime = now()
        WHERE name='Max calls';
      END IF;
    ELSE
      INSERT INTO Event_counters values('Max calls',now(),1);
    END IF;
    IF (select count(*) from Event_counters WHERE name='Max calls 5min' and eventtime =
	from_unixtime(floor(unix_timestamp(now())/300)*300)) > 0 THEN
      IF (select value from Event_counters WHERE name='Max calls 5min' and eventtime =
	  from_unixtime(floor(unix_timestamp(now())/300)*300)) <
	  (select value from Event_counters WHERE name='Current calls') THEN
        select value into @temp_counter from Event_counters WHERE name='Current calls';
        UPDATE Event_counters SET value = @temp_counter WHERE name='Max calls 5min' and eventtime =
		from_unixtime(floor((unix_timestamp(now())/300))*300);
      END IF;
    ELSE
        select value into @temp_counter from Event_counters WHERE name='Current calls';
        INSERT INTO Event_counters values ('Max calls 5min',
		from_unixtime(floor(unix_timestamp(now())/300)*300),@temp_counter);
    END IF;
  END IF;
  IF (NEW.eventtype='end') THEN
    UPDATE Event_counters SET value = value - 1 WHERE name='Current calls';
    IF (select count(*) from Event_counters WHERE name='Max calls 5min' and eventtime =
	from_unixtime(floor(unix_timestamp(now())/300)*300)) = 0 THEN
      select value into @temp_counter from Event_counters WHERE name='Current calls';
      INSERT INTO Event_counters values ('Max calls 5min',
	  from_unixtime(floor(unix_timestamp(now())/300)*300),@temp_counter);
    END IF;
  END IF;
END;|
DELIMITER ;

As a bonus these counters will also give you the time of the maximum count and the time of the last call. With a bit of tweaking you can also get the exact time within each 5-minute-period when the maximum was reached, but I'll leave that up to you ;-)