SQL Query Average Over Time

Hello,

I have a basic table in MySQL

Timestamp - datetime
Value - double

I have data for every minute stored in this table, for example:

1/1/2014 12:00:00
1/1/2014 12:01:00
1/1/2014 12:02:00 ....

I'd like to write a query that will return the average 'Value' of each hour, for the past 60 hours.  

Any suggestions on the best approach?
compsol1993Asked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
over a 60 hour period you would need to cater for both DATE() and HOUR()

e.g.

select
  date(`myTimestamp`) as day_of
, hour(`myTimestamp`) as hour_of
, avg(`some_value`) as avg_value
from tab1
where myTimestamp > '$ago' --  borrowing from above
group by
  date(`myTimestamp`)
, hour(`myTimestamp`)
0
 
Ray PaseurConnect With a Mentor Commented:
To get the value of 60 hours ago, you might use something like this:

$ago = date('c', strtotime('NOW - 60 HOURS'));

Your query would have a WHERE clause saying something like:

WHERE myTimestamp > '$ago'

You can get the hour value from the myTimestamp column with the HOUR() function.

You can get the average value from a column with the AVG() function.

I don't have a test data set to give you an example, but it seems like you can write a GROUP clause to group the average values by the hours.  If you want to post a CREATE TABLE statement and a test data set that provides easy verification, I'll be glad to write a small script that loads the table and runs the query.
0
 
Ray PaseurCommented:
@PortletPaul: Good point!  I might have come to that realization when tried to test and discovered that days have fewer than 60 hours!

:-)

Ray
0
 
PortletPaulfreelancerCommented:
:) happy to chip in Ray. Cheers.
0
 
compsol1993Author Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.