Calculating 1/2 hour averages

Simple Man

Member
Joined
Jul 7, 2010
Messages
13
Programming Experience
5-10
Looking for some ideas on how to average my data by 1/2 increments.

We've got a weather station and the data is written to a sql database every 2 minutes. I need to someone average the data by 1/2 hour increments. So, data between 3pm and 3:30pm need to be averaged for the 3:30pm time. 3:30pm to 4pm needs to be avg for the 4pm reading, etc.

The 1/2 readings will be for a data export that I will provide based on date criteria.

Anyone have an idea on how I should approach this? Not sure if I should do this using SQL or just code it in vb.net and then export it.

Doing this hourly would be a lot simpler because I could group it by the hour....but having the do this for 1/2 hours throws a little wrinkle in it.

Thoughts?

Thanks.

Nick
 

ss7thirty

Well-known member
Joined
Jun 14, 2005
Messages
455
Location
New Jersey, US
Programming Experience
5-10
You will want to use a SQL Aggregate function AVG and group you data based on the time field.

For the first half hour
VB.NET:
select AVG(TheFieldYouWantToAVG),Year(snapshotdate), Month(snapshotdate), Day(snapshotdate),  Datepart(Hour,Snapshotdate), Datepart(Minute, snapshotdate)
from YourTable 
GROUP BY Year(snapshotdate), Month(snapshotdate), Day(snapshotdate), Datepart(Hour,Snapshotdate), Datepart(Minute, snapshotdate)
HAVING Datepart(Minute, snapshotdate) <= 30

For the second half hour
VB.NET:
select AVG(TheFieldYouWantToAVG),Year(snapshotdate), Month(snapshotdate), Day(snapshotdate),  Datepart(Hour,Snapshotdate), Datepart(Minute, snapshotdate)
from YourTable 
GROUP BY Year(snapshotdate), Month(snapshotdate), Day(snapshotdate), Datepart(Hour,Snapshotdate), Datepart(Minute, snapshotdate)
HAVING Datepart(Minute, snapshotdate) > 30
 
Top Bottom