average daily

[emerson]

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?

[guilherme]

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 |