SQL Hourly Data

Question

The query below retrieves weather data from a MySql database, and groups this data in to an hourly format. ``` select hour(datetime) AS hour , avg(Temperature) as AVGT from Database.minute WHERE DATETIME BETWEEN (CURDATE() + INTERVAL (SELECT hour(NOW())) hour - INTERVAL 23 hour) AND ((CURDATE() + INTERVAL (SELECT hour(NOW())) hour)) group by hour order by (CURDATE() + INTERVAL (SELECT hour(NOW())) hour - INTERVAL 23 hour) ``` Output is as follows: ``` hour AVGT 19 11.730 20 11.970 21 11.970 22 11.760 23 11.660 0 11.700 1 11.830 2 12.370 3 12.770 4 12.840 5 12.840 6 12.540 7 12.500 8 12.030 9 12.100 10 12.300 11 12.060 12 11.090 13 10.920 14 10.920 15 10.820 16 10.760 17 10.690 18 10.560 ``` The time is now 18:15. All of the above output is correct apart from the data gathered for hour '18'. Instead of getting the average value between 18:00 and 18:15, it just outputs the average at time 18:00. ie. ignoring data between 18:01 and 18:14. How can I modify the above query to include data…

Accepted Answer

Why don't you simply try

```
SELECT Hour(datetime)   AS hour, 
       Avg(temperature) AS AVGT 
FROM   DATABASE.minute 
WHERE  datetime BETWEEN ( Curdate() + INTERVAL (SELECT Hour(Now())) hour - 
                          INTERVAL 23 hour ) AND Now() 
GROUP  BY hour 
ORDER  BY ( Curdate() + INTERVAL (SELECT Hour(Now())) hour - INTERVAL 23 hour ) 
```

SQL Hourly Data

Solution

Problem