Use event schedule and stored procedure to automatically average every hour MySQL

Question

I have a database in Mysql Workbench about weather that updates every second. It contains temperature and wind speed. This is my database:

CREATE TABLE `test`.`new_table` (
`id` INT(10) NOT NULL,
`date` DATETIME NOT NULL,
`temperature` VARCHAR(25) NOT NULL,
`wind_speed` VARCHAR(25) NOT NULL,
`humidity` VARCHAR(25) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

I want to find the average temperature every hour and insert the result into a new table like this

CREATE TABLE `test`.`table1` (
`idsea_state` INT(10) NOT NULL,
`dateavg` DATETIME NOT NULL,
`avg_temperature` VARCHAR(25) NOT NULL,
PRIMARY KEY (`idsea_state`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

this is my coding to find average and insert the result into new table

INSERT INTO `table1`
(`dateavg`, `avg_temperature`) 
SELECT `date` , avg(`temperature`)
FROM   `new_table`
GROUP BY DATE( date ), HOUR( date );

and this is my code for stored procedure

USE `test`;
DROP procedure IF EXISTS `new_procedure`;

DELIMITER $$ 
USE `test`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`()  
BEGIN
  INSERT INTO `table1`
   (`dateavg`, `avg_temperature`) 
  SELECT `date` , avg(`temperature`)
  FROM   `new_table`
  GROUP By DATE( date ), HOUR( date );
 END$$

 DELIMITER ;

the problem is I want this coding average run automatically every hour, should I use stored procedure and event scheduler? Please help me, I don't know how to use event scheduler in Mysql Workbench.

score 5 · Answer 1 · answered Jun 08 '15 at 14:36

Run this in MySQL

SET GLOBAL event_scheduler = ON;

Add this to my.cnf so the scheduler remains enabled upon mysql restart

[mysqld]
event_scheduler = ON

Start the Event on the Next Hour, Make it Recurring Every Hour, Adjusting WHERE clause

CREATE EVENT hourly_temp_avg
    ON SCHEDULE
    EVERY HOUR
    STARTS DATE(NOW()) + INTERVAL (HOUR(NOW())+1) HOUR
    ON COMPLETION PRESERVE
DO
INSERT INTO `table1` (`dateavg`, `avg_temperature`) 
SELECT `DATE` , avg(`temperature`)
FROM `new_table`
WHERE `date` >= NOW() - INTERVAL 1 HOUR;

Masoud came up with the proper concept first. So, he gets +1 from me.

Masoud · Answer 2 · 2015-06-08T04:02:12.330

Make sure the event scheduler is on

SET GLOBAL event_scheduler = ON;

and then create the event as below:

CREATE EVENT hourly_temp_avg
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
INSERT INTO `table1`
   (`dateavg`, `avg_temperature`) 
  SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %h:00:00') , avg(`temperature`)
  FROM   `new_table`
  WHERE 
  `date` BETWEEN DATE_FORMAT(NOW()  - INTERVAL 1 HOUR, '%Y-%m-%d %h:00:01') AND DATE_FORMAT(NOW(), '%Y-%m-%d %h:00:00');

this event will run every hour and stores the past one hour avg in table1.

Use event schedule and stored procedure to automatically average every hour MySQL

2 Answers2

Linked