Hourly averages from a table with multiple rows per hour

Dev Shed Forums Sponsor:

January 5th, 2013, 04:24 AM

phpcog2

Contributing User

Join Date: Mar 2011

Posts: 39

Time spent in forums: 8 h 24 m 55 sec
Reputation Power: 3

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.

January 5th, 2013, 04:37 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,353

Time spent in forums: 3 Months 1 Week 2 Days 4 h 17 m 19 sec
Reputation Power: 4140

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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

January 5th, 2013, 08:47 AM

phpcog2

Contributing User

Join Date: Mar 2011

Posts: 39

Time spent in forums: 8 h 24 m 55 sec
Reputation Power: 3

That is amazing!

Thank you very much.

Viewing: Dev Shed Forums > Databases > MySQL Help > Hourly averages from a table with multiple rows per hour

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump