Search code examples
mysqlsqlmoving-average

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.

Solution

  • 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;