SQL Hourly Data
Solution 1
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 )
Solution 2
I agree with @Ankur's answer (your filter citerion should not filter records up to the current hour, but rather the current time), however your date/time operations are very strange:
You don't need a subquery
(SELECT Hour(NOW()))
to obtainHOUR(NOW())
;-
You can express
( Curdate() + INTERVAL (SELECT Hour(NOW())) hour - INTERVAL 23 hour )
more simply:CURDATE() + INTERVAL HOUR(NOW()) - 23 HOUR
Or, in my view, more clearly:
DATE_FORMAT(NOW() - INTERVAL 23 HOUR, '%Y-%m-%d %H:00:00')
Your
ORDER BY
clause is a constant and therefore achieves nothing: did you mean to order byhour
?
Therefore:
SELECT HOUR(datetime) AS hour,
AVG(Temperature) AS AVGT
FROM Database.minute
WHERE datetime BETWEEN
DATE_FORMAT(NOW() - INTERVAL 23 HOUR, '%Y-%m-%d %H:00:00')
AND NOW()
GROUP BY hour
ORDER BY hour
Related videos on Youtube
mmmbaileys
Updated on September 15, 2022Comments
-
mmmbaileys about 1 year
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 in the current hour (18:00 to Now)?
Thanks
-
Admin about 11 yearsPlease also summarize the difference(s) and reasoning.
-
mmmbaileys about 11 yearsThanks a lot Ankur! Appreciate it.
-
Ankur about 11 yearsTotally agree, much better way
-
mmmbaileys almost 11 yearsThanks both of you. Given the detailed answer and reasoning of eggyal's answer, along with his suggestion, I'll mark this as my accepted answer. Cheers