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.
Best Answer
Run this in MySQL
Add this to
my.cnf
so the scheduler remains enabled upon mysql restartStart the Event on the Next Hour, Make it Recurring Every Hour, Adjusting
WHERE
clauseMasoud came up with the proper concept first. So, he gets +1 from me.