how to add hourly stats of the max servers/channels/users for the last X days to anope denoras table format (3 separate tables for users/channels/servers) ----------------------------------------------------------- (00:00) (01:00) (23:00) id day month year time_0 time_1 ... time_23 ----------------------------------------------------------- 1 15 12 2013 155 154 156 2 16 12 2013 157 150 153 proposal for a new table format??? (example) ------------------------------------- type time count -------------------------------------- servers 2013-12-15 13:00 7 servers 2013-12-15 14:00 7 users 2013-12-15 13:00 159 users 2013-12-15 14:00 155 channels 2013-12-15 13:00 47 channels 2013-12-15 14:00 46 #anope 2013-12-15 13:00 67 #anope 2013-12-15 14:00 68 we can create an hourly event: (example) ************************************ DROP EVENT magirc_event_hourly_history; CREATE EVENT magirc_event_hourly_history ON SCHEDULE EVERY 1 HOUR STARTS HOUR(NOW())+1 DO INSERT ON DUPLICATE KEY UPDATE set count = (SELECT COUNT(*) FROM server/user/channel) END; ******************************************* I suggest adding the table(s) and the event from magirc, this should not be part of anope. **************************************************************************** CREATE TABLE magirc_history ( type varchar(255) NOT NULL, time datetime NOT NULL, count int NOT NULL PRIMARY KEY (`type`, `time`), KEY (`type`) ) ENGINE = MyISAM DEFAULT CHARSET=utf8; DROP EVENT magirc_event_hourly_history; CREATE EVENT magirc_event_hourly_history ON SCHEDULE EVERY 1 HOUR STARTS HOUR(now())+1 DO DECLARE count_ int; DECLARE date_time now_; SELECT subtime(now(), extract(minute_second from now())) INTO now_; /* servers */ SELECT COUNT(id) INTO count_ FROM anope_server; INSERT INTO `magirc_history` (type, time, count) VALUES ("servers", now_, count_) ON DUPLICATE KEY UPDATE type=VALUES(type), time=VALUES(time), count=VALUES(count); /* channels */ SELECT COUNT(chanid) INTO count_ FROM anope_chan; INSERT INTO `magirc_history` (type, time, count) VALUES ("channels", now_, count_) ON DUPLICATE KEY UPDATE type=VALUES(type), time=VALUES(time), count=VALUES(count); /* users */ SELECT COUNT(nickid) INTO count_ FROM anope_user; INSERT INTO `magirc_history` (type, time, count) VALUES ("users", now_, count_) ON DUPLICATE KEY UPDATE type=VALUES(type), time=VALUES(time), count=VALUES(count); /* history for each channel */ my_cursor: BEGIN DECLARE no_more_rows BOOLEAN DEFAULT FALSE; DECLARE chan_ VARCHAR(255); DECLARE channel_cursor CURSOR FOR SELECT chan FROM anope_chan END;