How to Generate a Series of Hourly Averages in MySQL

How do I generate a series of hourly averages in MySQL?

It's unclear whether you want the average to be aggregated over days or not.

If you want a different average for midnight on the 26th vs midnight on the 27th, then modify Mabwi's query thus:

SELECT AVG( value ) , thetime
FROM hourly_averages
GROUP BY DATE( thetime ), HOUR( thetime )

Note the additional DATE() in the GROUP BY clause. Without this, the query would average together all of the data from 00:00 to 00:59 without regard to the date on which it happened.

How to get average value every hour in a day

Just group by DATE(time), HOUR(time):

SELECT DATE(time) AS date
     , HOUR(time) AS hour
     , AVG(temprature)
FROM t
GROUP BY 1, 2

Or this:

SELECT time
     - INTERVAL EXTRACT(MINUTE FROM time) MINUTE
     - INTERVAL EXTRACT(SECOND FROM time) SECOND AS date_hour
     , AVG(temprature)
FROM t
GROUP BY 1

Find average value for each hour interval in a certain time period

Hmmm . . . I'm not familiar with all the functions, but it seems you want the AVG() aggregation function:

SELECT TIME_FLOOR(__time, 'PT1h') AS "__time_time_floor",
       AVG("value"), COUNT(*) AS "Count"
FROM "database"
WHERE "__time" >= CURRENT_TIMESTAMP - INTERVAL '1' DAY AND "device" = 'device_1'AND
      "metric"='metric_1'
GROUP BY 1
ORDER BY "__time_time_floor" DESC;

Basically, this removes VALUE from the GROUP BY.

MySQL average value of each hour for the last 30 days

You can just use the hour() function:

select hour(timestamp) as hh, avg(sb1_ac_ges_diff)
from t
group by hh;

You can convert this to a string or time if you want, but that does not seem useful to me.

If you actually want the hour for each day, then:

select date(timestamp) as dd, hour(timestamp) as hh, avg(sb1_ac_ges_diff)
from t
group by dd, hh;

Get the hourly average amount based on rows with minutely timestamp

We can use an expression that returns a representation of hour, basically trimming off minutes and seconds, and then GROUP BY that expression. One possibility it to use the MySQL DATE_FORMAT function.

 SELECT DATE_FORMAT(FROM_UNIXTIME(t.timestamp),'%Y-%m-%d %H') AS ts_hr
      , AVG(t.hashes)
      , AVG(t.shares)
   FROM ( 
             -- original query goes here as inline view
             SELECT timestamp
                 , SUM(hashrate) as hashes
                 , SUM(sharerate) AS shares
              FROM statistics_users
             WHERE FROM_UNIXTIME(timestamp) >= NOW() + INTERVAL -1 DAY
             GROUP BY timestamp

        ) t 
  GROUP 
     BY DATE_FORMAT(FROM_UNIXTIME(t.timestamp),'%Y-%m-%d %H')
  --    ^^^^^^^^^^^^                          ^^^^^^^^^^^^^^^

Note that the condition in the WHERE clause doesn't respect hour boundaries, so we're going to get a partial result for first hour, and a partial last hour.

The specification isn't clear, as to whether we want the average of the sums for each timestamp, or more simply

 SELECT DATE_FORMAT(FROM_UNIXTIME(t.timestamp),'%Y-%m-%d %H') AS ts_hr
      , AVG(t.hashrate)
      , AVG(t.sharerate)
   FROM statistics_users t
  WHERE FROM_UNIXTIME(t.timestamp) >= NOW() + INTERVAL -1 DAY
  GROUP 
     BY DATE_FORMAT(FROM_UNIXTIME(t.timestamp),'%Y-%m-%d %H')
 --     ^^^^^^^^^^^^                          ^^^^^^^^^^^^^^^ 

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

How to get average value for each hourly increment that is split into 5 minute intervals

You didn't specify the type for interval_time, so I'm assuming a string, you can parse it out with a case statement like this:

SELECT interval_date,
       CASE WHEN SUBSTRING(interval_time,4,2)='00' THEN interval_time
            WHEN SUBSTRING(interval_time,1,2)='23' THEN '00:00'
            ELSE FORMAT(convert(int,SUBSTRING(interval_time,1,2))+1,'00')+':00'
       END interval_time,
       AVG(power)
 FROM mytable
WHERE on_status = 'Y'
GROUP BY interval_date,
       CASE WHEN SUBSTRING(interval_time,4,2)='00' THEN interval_time
            WHEN SUBSTRING(interval_time,1,2)='23' THEN '00:00'
            ELSE FORMAT(convert(int,SUBSTRING(interval_time,1,2))+1,'00')+':00'
       END

Note that to get your target of 5.17, I had to comment out the on_status = 'Y' filter.

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=b604e4fe6696465aac75676e69b92a47

MySQL get average values per hour from the last 24 hours

You have to mention the last 24 hours data:

SELECT id, serverID, AVG(performance) as performance, AVG(online) as online, 
HOUR(timestamp) FROM stats_server
WHERE serverID= :serverID AND DATE_SUB(`timestamp`,INTERVAL 1 HOUR) And 
timestamp > DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY HOUR(timestamp)
ORDER BY id ASC

---

Related Topics