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

5

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.

answered Jun 08 '15 at 14:36

RolandoMySQLDBA

182,700
33
317
520

It was just this check, I was in the same scenario with my Procedure, its important to SHOW EVENTS\G; after turn it on and also check if yourEvent is created SHOW CREATE EVENT yourevent\G – Ricardo Rivera Nieves Oct 11 '20 at 02:48
For mysql 8, EVERY HOUR requires a number to it, so this should be EVERY 1 HOUR. – FlyingZebra1 Nov 29 '20 at 03:23

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

3

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.

edited Jun 08 '15 at 04:02

answered May 18 '15 at 04:07

Masoud

491
1
6
16

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 06: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 08 '15 at 02: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 08 '15 at 02: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 08 '15 at 04:09
Im not trying to avoid calculating a value twice. I am just trying to make the summary of exact one hour range and with exact i mean for example from 1 to 1:59 pm rather than say 1:48 pm to 2:47 PM. I hope i made myself clear :) – Masoud Jun 08 '15 at 04:32
Oh disregard my previous comments. I have just read the question again and you are right, they want to group by the hour. I did not notice the first time. Your query is not what I'd have used, but it's a valid way to "truncate" to the hour. – ypercubeᵀᴹ Jun 08 '15 at 14:53

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

2 Answers2

Linked