Mysql group by hour don't work with week range

Question

I have time range in database:

date                   temp
2014-05-09 20:40:01    19.6875
2014-05-09 20:50:01    19.375
.....................
2014-05-10 00:10:01    17.5
........................
2014-05-23 08:25:01    27.4375

And i want get all AVG temperature with week group by hour. Here sql query:

SELECT AVG( `temp` ) , `date` FROM `temperature` WHERE `date` BETWEEN '2014-05-16 11:06' AND '2014-05-23 11:06' GROUP BY HOUR( `date` )

But in result i have only value with range from 2014-05-16 23:06:02 to 2014-05-17 00:05:01

And not have error.

Help me find my mistake.

Sory for my bad English

Answer 1

This is because when you use GROUP BY and display a field that isn't being grouped, mysql can't show you all the values of that field. So it just shows one.

This query makes more sense:

SELECT AVG( `temp` ) , HOUR(`date`) FROM `temperature` WHERE `date` BETWEEN '2014-05-16 11:06' AND '2014-05-23 11:06' GROUP BY HOUR( `date` )

Or this one (after discussion)

SELECT AVG( temp ) ,
    YEAR(date),
    MONTH(date),
    DAY(date),
    HOUR(date)
FROM temperature
WHERE date BETWEEN '2014-05-16 11:06' AND '2014-05-23 11:06'
GROUP BY YEAR(date),MONTH(date),DAY(date),HOUR(date)