Solved

SQL Query Average Over Time

Posted on 2014-04-23
5
525 Views
Last Modified: 2014-05-15
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?
0
Comment
Question by:compsol1993
  • 2
  • 2
5 Comments
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
ID: 40018766
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 40019405
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40020078
@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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40020247
:) happy to chip in Ray. Cheers.
0
 

Author Closing Comment

by:compsol1993
ID: 40068030
Thanks!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now