Reputation: 1
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
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