0

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.

2

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.

  • 1
    I would recommend that you not use the word "date" as a fieldname. It makes your code non-portable and, more importantly perhaps, makes debugging more difficult. Use "my_date" or "the_date" or similar. – Vérace May 18 '15 at 19:25
  • you are certainly right. I just followed Tia naming convention to avoid confusion. – Masoud May 19 '15 at 6:51
  • 1
    You don't need to format dates to strings: WHERE date BETWEEN NOW() - INTERVAL 1 HOUR AND NOW() would be fine (and avoiding unnecessary conversion back and forth). – ypercubeᵀᴹ Jun 8 '15 at 2:07
  • date is in datetime format. if he creates the event on let's say 11:23:00 next one hour will be 12:23:00 (when the even will be executed). so the where clause will consider the time too. So I am trying to get the exact one hour avg (11:00:00 to 12:00:00) rather than 11:23:00 to 12:23:00 – Masoud Jun 8 '15 at 2:45
  • Date and datetime values are not stored in a string format. If you are trying to avoid selecting some values twice, you can use a including-excluding range (which is usually better than BETWEEN for this reason exactly). Something like: WHERE date > NOW() - INTERVAL 1 HOUR AND date <= NOW() – ypercubeᵀᴹ Jun 8 '15 at 4:09
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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