#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    53
    Rep Power
    4

    Hourly averages from a table with multiple rows per hour


    Hi everyone

    I have a table with rows of temperature data. 2 columns - datetime (which is a datetime column) and temp1.

    To get the hourly averages at the moment I use a PHP loop to increment start and end times and run multiple queries.

    Is it possible to get hourly averages in one query?

    Thank you.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,140
    Rep Power
    4274
    Code:
    SELECT start_time
         , start_time + INTERVAL 1 HOUR AS end_time
         , AVG(temp1) AS avg_temp
      FROM ( SELECT FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(`datetime`)/3600)) AS start_time
                  , temp1
               FROM daTable ) AS data
    GROUP
        BY start_time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    53
    Rep Power
    4
    That is amazing!

    Thank you very much.

IMN logo majestic logo threadwatch logo seochat tools logo