# average daily

• There's a table that says time and temperature every two minutes.

`````` INSERT INTO `stats` (`stats_time`, `stats_t`) VALUES
(1450537200, 12),
(1450537320, 14),
(1450537440, 13);
``````

avg gives a mean value in general.
Question: How do we remove the mass with average daily value in one month?

• Judging by the operator you commanded. insert, you keep time in a numerical column. s1 - it's done.

But it might be a good choice to use. https://dev.mysql.com/doc/refman/5.5/en/date-and-time-types.html as in the second example, with the table s2

to work with the date and time https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html which can be used for your request.

I've set an example for selection only one day and with an hour-by-hour group (because of the scantness of the data you've provided), and I hope that there will be little work to replace the functions and dates.

http://sqlfiddle.com/#!9/20ec79/5

MySQL 5.6 Schema Setup:

``````create table s1 (stime int, stemp int);
insert into s1 values
(1450537200, 12)
,(1450537320, 14)
,(1450537440, 13)
;
create table s2 (stime datetime, stemp int);
insert into s2 values
(from_unixtime(1450537200), 12)
,(from_unixtime(1450537320), 14)
,(from_unixtime(1450537440), 13)
;
``````

Query 1:

``````select avg(stemp), hour(from_unixtime(stime))
from s1
where from_unixtime(stime) between '2015-12-19' and '2015-12-20'
group by hour(from_unixtime(stime))
``````

http://sqlfiddle.com/#!9/20ec79/5/0 :

``````| avg(stemp) | hour(from_unixtime(stime)) |
|------------|----------------------------|
|         13 |                         15 |
``````

Query 2:

``````select avg(stemp), hour(stime)
from s2
where stime between '2015-12-19' and '2015-12-20'
group by hour(stime)
``````

http://sqlfiddle.com/#!9/20ec79/5/1 :

``````| avg(stemp) | hour(stime) |
|------------|-------------|
|         13 |          15 |
``````

2

2

2

2

2

2

2

2

2

2

2

2

2

2

2