SQL Hourly Data

10,489

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:

  1. You don't need a subquery (SELECT Hour(NOW())) to obtain HOUR(NOW());

  2. 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')
    
  3. Your ORDER BY clause is a constant and therefore achieves nothing: did you mean to order by hour?

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
Share:
10,489

Related videos on Youtube

mmmbaileys
Author by

mmmbaileys

Updated on September 15, 2022

Comments

  • mmmbaileys
    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
    Admin about 11 years
    Please also summarize the difference(s) and reasoning.
  • mmmbaileys
    mmmbaileys about 11 years
    Thanks a lot Ankur! Appreciate it.
  • Ankur
    Ankur about 11 years
    Totally agree, much better way
  • mmmbaileys
    mmmbaileys almost 11 years
    Thanks 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