CODEWITHSUNDEEP
mysqlsqlmoving-average
Viking_03
Viking_03

Reputation: 43

MySQL average value of each hour for the last 30 days

I have a table, that is updated every minute and I need to calculate the average value of each hour, for the values of the last 30 days.

Timestamp            | SB1_AC_GES_DIFF
2020-07-14 15:13:04     30
2020-07-14 15:12:07     27
...                     ...

I want to save the results in a second table named avgTable like this

Timestamp            | AVG_SB1
15:00                  29
16:00                  32
...                    ...

It would be perfect if the table could update itself once a day, maybe when it's 12 o'clock and the the date part for the day changes.

Upvotes: 0

Views: 1501

Answers (2)

Dr. Mantis Tobbogan
Dr. Mantis Tobbogan

Reputation: 538

You can try:

INSERT INTO avg_table 
SELECT Date_format(Timestamp, "%h:00:00") AS HourlyTimeStamp, 
       Avg(sb1_ac_ges_diff) AS "AVG_SB1" 
FROM   table
WHERE Timestamp between DATEADD(DAY, -30, GETDATE()) AND GETDATE()
GROUP BY 1

Assuming that you want the average rolling average, agnostic of the day.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

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;

Upvotes: 0

Related Questions