How to get average value every hour in a day

Question

I am trying to get average temperature value every hour in a day. so far, i only managed to only get the average temperature in a day (overall, not in every hour) using the AVG function. the data is dynamic so i can't do query to specify a time range. here's my table.

-----------------------------------------
| time          |   temprature          |
-----------------------------------------
| 2019-01-11 12:00:00   |   23          |
| 2019-01-11 12:20:00   |   24          |
| 2019-01-11 12:40:00   |   24          |
| 2019-01-11 13:00:00   |   25          |
| 2019-01-11 13:50:00   |   26          |
| 2019-01-11 14:12:00   |   25          |
| 2019-01-11 14:14:00   |   27          |
| 2019-01-11 14:14:00   |   27          |
| 2019-01-11 14:16:00   |   29          |
| 2019-01-11 14:18:00   |   30          |
| 2019-01-11 15:37:00   |   27          |
| 2019-01-11 16:00:00   |   24          |

this is the expected result of the returned query.

-------------------------------------
| time                  |temprature|
-----------------------------------
| 2019-01-11 12:00:00   |   23.6    |
| 2019-01-11 13:00:00   |   25.5    |
| 2019-01-11 14:00:00   |   27.6    |
| 2019-01-11 15:00:00   |   27      |
| 2019-01-11 16:00:00   |   24      |

Answer 1

Just group by DATE(time), HOUR(time):

SELECT DATE(time) AS date
     , HOUR(time) AS hour
     , AVG(temprature)
FROM t
GROUP BY 1, 2

Or this:

SELECT time
     - INTERVAL EXTRACT(MINUTE FROM time) MINUTE
     - INTERVAL EXTRACT(SECOND FROM time) SECOND AS date_hour
     , AVG(temprature)
FROM t
GROUP BY 1