CODEWITHSUNDEEP
sqlmysqlaverage
Ivo
Ivo

Reputation: 31

calculate averages for 5 min intervals in mysql

I have a table log with columns id, myValue, myCategory and myTimestamp. Suppose the table is filled like this:

ID MYVALUE MYCATEGORY MYTIMESTAMP
1 10 1 2010-11-1 10:00:00
2 20 1 2010-11-1 10:03:00
3 15 2 2010-11-1 10:15:00
4 05 2 2010-11-1 10:19:00
5 30 1 2010-11-1 10:24:00
6 12 1 2010-11-1 10:30:00

Now I would like to generate a table with an avg() for column myValue in 5 minute intervals for a specified myCheck, starting from the last check entry.

The output for myCheck = 1 should be like:

ID AVERAGE
1 12
2 30
3 15

The output for myCheck = 2 should be like:

ID AVERAGE
1 10

What is the best way to approach this? I have basic knowledge of MySQL but this puzzles me. Part of the query will be like this I suppose (without the 5 min interval grouping):

SELECT
  avg(myValue)
FROM
  log
WHERE
  myCheck = ..

How can I use the timestamps to generate 5 min intervals averages? Any help is greatly appreciated.

Upvotes: 2

Views: 7552

Answers (4)

Jaime M.
Jaime M.

Reputation: 3823

Maybe later but a shortest query would be:

 SELECT ID, AVG(VALUE) as average, DATE_FORMAT(MIN(timestamp), '%Y-%m-%d %H:%i:00') AS tmstamp
   FROM yourtable GROUP BY round(UNIX_TIMESTAMP(timestamp) DIV (60*5))

// use brackets behind DIV to multiply seconds for getting minutes

Upvotes: 2

Xodarap
Xodarap

Reputation: 11859

See this.

SELECT
  AVG(myValue),
  from_unixtime(ROUND(myTimestamp / (60*5)) * 60 * 5) as rounded_time
FROM
  myTable
GROUP BY rounded_time

Upvotes: 2

Prasanna Sujeewa
Prasanna Sujeewa

Reputation: 357

SELECT 
   DATE_FORMAT((atimestamp),
      concat( '%Y-%m-%d %H:' , 
            cast(round(minute(atimestamp)) -  round(minute(atimestamp)) % 5 as char))
   )AS rounded_time
   ,avg(price) , count(*) from table

This way you can get 5 , 10, 15, 30etc. minute average easily.

Upvotes: 0

Ivo
Ivo

Reputation: 1

In case someone else is interested here is the answer I've come up with (with help from a colleague).

First create a stored procedure in MysQL like so (original source modified from here http://ondra.zizka.cz/stranky/programovani/sql/mysql_stored_procedures.texy) :

DELIMITER $$
CREATE PROCEDURE generate_series (
  iFrom TIMESTAMP, iTo TIMESTAMP, iStep INTEGER )

body:
BEGIN

  DROP TEMPORARY TABLE IF EXISTS stamp_series;
  CREATE TEMPORARY TABLE stamp_series ( stamp TIMESTAMP NOT NULL);

  IF iFrom IS NULL OR iTo IS NULL OR iStep IS NULL
    THEN LEAVE body; END IF;

  SET @iMax = iFrom;
  SET @iMin = iTo;

  InsertLoop: LOOP
    INSERT INTO stamp_series SET stamp = @iMax;
    SET @iMax = DATE_SUB( @iMax, INTERVAL iStep SECOND);
    IF @iMin > @iMax THEN LEAVE InsertLoop; END IF;
  END LOOP;

END; $$
DELIMITER ;

then the actual query is like this:

CALL generate_series( timestamp1 , timestamp2 , 300);
SELECT stamp, DATE_SUB(stamp, INTERVAL 5 MINUTE) AS stamp_min_5, (  SELECT COALESCE( AVG(value), 0) FROM `table` WHERE `category` = 1 AND timestamp <= stamp AND timestamp > DATE_SUB(stamp, INTERVAL 5 MINUTE) ) AS gem FROM stamp_series;

Regards, Ivo

Upvotes: 0

Related Questions