Average per hour Mysql

Asked

Viewed 842 times

0

Hello, I wonder if you could return only the averages per hour of a table as below:

id | valor |     timestamp    |
1  | 5.4   | 2017-4-20 18:00  |
2  | 3     | 2017-4-20 18:01  |
3  | 2.8   | 2017-4-20 18:40  |
4  | 2     | 2017-4-20 19:00  |
5  | 10    | 2017-4-20 19:05  |
6  | 7     | 2017-4-20 19:20  |
7  | 1     | 2017-4-20 19:55  |
8  | 5     | 2017-4-20 20:00  |
9  | 7     | 2017-4-20 20:11  |
10 | 4     | 2017-4-20 20:50  |
11 | 6     | 2017-4-20 21:00  |
------------------------------

..would have any select to return the hourly averages from the "value" column? I am trying but unsuccessfully...

  • Yes, it’s in my answer

2 answers

1


SELECT AVG( valor ) , HOUR( `timestamp` )
FROM tabela
WHERE DATE_SUB(  `timestamp` , INTERVAL 1 HOUR )
GROUP BY HOUR( `timestamp` )

Upshot

inserir a descrição da imagem aqui

To catch the hourly averages of every day

SELECT AVG( valor ) , HOUR( `timestamp` ),day( `timestamp` )
FROM tabela
WHERE DATE_SUB(  `timestamp` , INTERVAL 1 HOUR )
GROUP BY HOUR( `timestamp` ), day( `timestamp` )


id | valor |     timestamp    |
1  | 5.4   | 2017-4-20 18:00  |
2  | 3     | 2017-4-20 18:01  |
3  | 2.8   | 2017-4-20 18:40  |
4  | 2     | 2017-4-20 19:00  |
5  | 10    | 2017-4-20 19:05  |
6  | 7     | 2017-4-20 19:20  |
7  | 1     | 2017-4-20 19:55  |
8  | 5     | 2017-4-20 20:00  |
9  | 7     | 2017-4-20 20:11  |
10 | 4     | 2017-4-20 20:50  |
11 | 6     | 2017-4-20 21:00  |
12 | 4     | 2017-4-21 21:08  |
13 | 6     | 2017-4-21 21:10  |
14 | 3     | 2017-4-21 21:25  |
15 | 6     | 2017-4-21 22:15  |
16 | 4     | 2017-4-21 22:25  |
17 | 4     | 2017-4-21 22:35  |
------------------------------

inserir a descrição da imagem aqui

  • But it is returning the averages of only one day, as I do to get the averages per hour of all days of mysql database?

  • You’re the guy! Thank you so much! I would never get this whole logic, thanks! Hug!

0

Browser other questions tagged

You are not signed in. Login or sign up in order to post.