nektarios basdekis
nektarios basdekis

Reputation: 1

How to store averages per hour in a new table?

I have the following table in mySQL, php:

table1:

Value1 Timestamp
312    2021-05-25 16:33:15
350    2021-05-25 16:33:25
411    2021-05-25 16:33:35
308    2021-05-25 16:33:45
etc

This table stores values in real time, every 10 seconds.

I would like to take the hourly averages of the values in table1 and store them in another table:

table2:

Value2 Timestamp
325    2021-05-25 16:59:59
330    2021-05-25 17:59:59
289    2021-05-25 18:59:59

I assume that I have to store the previous' hour average because the current hour's average is still changing. And that I have to store a value, once per hour.

Upvotes: 0

Views: 117

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

You can truncate to the hour using:

select
    cast(date_format(timestamp, '%Y-%m-%d %H:%i') as timestamp) as hh,
    avg(value1)
from t
group by hh;

You can insert into a new table or use create table as.

Upvotes: 1

Related Questions