Averaging values taken every 30 seconds into 5 minute intervals

M

Mark Rowe

I have a table (called DataRate) that consists of 2 fields (Time_Stamp and
Throughput), the first one records the time (data type = Date/Time) a sample
was taken and the second field records the amount of data transmitted (data
type = Number).

Samples are taken every 30 seconds and I need a query that will return the
average data transmitted every 5 minutes. Can anyone help?

Thanks,

Mark.
 
A

Amy Blankenship

Mark Rowe said:
I have a table (called DataRate) that consists of 2 fields (Time_Stamp and
Throughput), the first one records the time (data type = Date/Time) a
sample
was taken and the second field records the amount of data transmitted
(data
type = Number).

Samples are taken every 30 seconds and I need a query that will return the
average data transmitted every 5 minutes. Can anyone help?
Click to expand...

Make a query based on the your and add a field to it that calculates this
expression:

DateDiff("n", StartingTime, [Time_Stamp]) + 1 Mod 5

This will have the effect of giving each five-minute group its own "ID".
Noted I have no idea how you're specifying where this starts, so I've just
used StartingTime. This could be a subselect or a repeat of your parameter
if you're using a parameter query.

Now, you can make a new query based on the first query and just group and
average in the normal way.

HTH;

Amy
 
A

Amy Blankenship

Amy Blankenship said:
Mark Rowe said:
I have a table (called DataRate) that consists of 2 fields (Time_Stamp and
Throughput), the first one records the time (data type = Date/Time) a
sample
was taken and the second field records the amount of data transmitted
(data
type = Number).

Samples are taken every 30 seconds and I need a query that will return
the
average data transmitted every 5 minutes. Can anyone help?
Click to expand...

Make a query based on the your and add a field to it that calculates this
expression:

DateDiff("n", StartingTime, [Time_Stamp]) + 1 Mod 5
Click to expand...

Sorry, my math was off on that. You need to use

Round(DateDiff("n", StartingTime, [Time_Stamp]) /5) + 1
 
A

Amy Blankenship

Amy Blankenship said:
Amy Blankenship said:
Mark Rowe said:
I have a table (called DataRate) that consists of 2 fields (Time_Stamp
and
Throughput), the first one records the time (data type = Date/Time) a
sample
was taken and the second field records the amount of data transmitted
(data
type = Number).

Samples are taken every 30 seconds and I need a query that will return
the
average data transmitted every 5 minutes. Can anyone help?
Click to expand...

Make a query based on the your and add a field to it that calculates this
expression:

DateDiff("n", StartingTime, [Time_Stamp]) + 1 Mod 5
Click to expand...

Sorry, my math was off on that. You need to use

Round(DateDiff("n", StartingTime, [Time_Stamp]) /5) + 1
Click to expand...

Still not right. I suck at math. Use CINT() instead of Round.

Paugh.
 
J

John W. Vinson

On Fri, 25 Jan 2008 16:02:01 -0800, Mark Rowe <Mark
I have a table (called DataRate) that consists of 2 fields (Time_Stamp and
Throughput), the first one records the time (data type = Date/Time) a sample
was taken and the second field records the amount of data transmitted (data
type = Number).

Samples are taken every 30 seconds and I need a query that will return the
average data transmitted every 5 minutes. Can anyone help?

Thanks,

Mark.
Click to expand...

As an alternative to Amy's DateDiff suggestion, I'd try a totals query using a
calculated field, the time blocked off to the nearest 288th of a day (five
minutes):

SELECT CLng(288*CDbl([Time_Stamp])) AS TimeBlock, Min([Time_Stamp]) AS When,
Avg([Throughput]) As AvgOfThrouhput
FROM DataRate
GROUP BY CLng(288*CDbl([Time_Stamp]))
WHERE <whatever criteria you want>;

John W. Vinson [MVP]
 
M

Marshall Barton

Amy said:
Mark Rowe said:
I have a table (called DataRate) that consists of 2 fields (Time_Stamp and
Throughput), the first one records the time (data type = Date/Time) a
sample
was taken and the second field records the amount of data transmitted
(data
type = Number).

Samples are taken every 30 seconds and I need a query that will return the
average data transmitted every 5 minutes. Can anyone help?
Click to expand...

Make a query based on the your and add a field to it that calculates this
expression:

DateDiff("n", StartingTime, [Time_Stamp]) + 1 Mod 5

This will have the effect of giving each five-minute group its own "ID".
Noted I have no idea how you're specifying where this starts, so I've just
used StartingTime. This could be a subselect or a repeat of your parameter
if you're using a parameter query.
Click to expand...


I think that should be:

DateDiff("n", StartingTime, [Time_Stamp]) \ 5
 
M

Mark Rowe

John W. Vinson said:
On Fri, 25 Jan 2008 16:02:01 -0800, Mark Rowe <Mark
I have a table (called DataRate) that consists of 2 fields (Time_Stamp and
Throughput), the first one records the time (data type = Date/Time) a sample
was taken and the second field records the amount of data transmitted (data
type = Number).

Samples are taken every 30 seconds and I need a query that will return the
average data transmitted every 5 minutes. Can anyone help?

Thanks,

Mark.
Click to expand...

As an alternative to Amy's DateDiff suggestion, I'd try a totals query using a
calculated field, the time blocked off to the nearest 288th of a day (five
minutes):

SELECT CLng(288*CDbl([Time_Stamp])) AS TimeBlock, Min([Time_Stamp]) AS When,
Avg([Throughput]) As AvgOfThrouhput
FROM DataRate
GROUP BY CLng(288*CDbl([Time_Stamp]))
WHERE <whatever criteria you want>;

John W. Vinson [MVP]
Click to expand...

Thanks John, Amy and Marshall for your quick replies and helping with this,
it really is appreciated.

I tried the query John provided without the WHERE clause and it worked for
the most part except for the fiirst row returned. There does not appear to be
a 5 minute period between rows 1 and 2 but from there on all appears fine.

Below I have shown the original table, with some random entries I used for
testing, and the results returned by the query. Do you have any ideas why the
frst row does not appear to be right? Also, is it possible to get the query
to return values starting at the hour - i.e. 01:00, 01:05, 01:10 etc..?

Original table "DataRate":

Time_Stamp Throughput
01/01/2007 01:01:00 542520
01/01/2007 01:01:05 252520
01/01/2007 01:01:05 5432525
01/01/2007 01:02:05 52455
01/01/2007 01:02:05 6536361
01/01/2007 01:02:05 65656
01/01/2007 01:03:05 767463634
01/01/2007 01:03:05 767335
01/01/2007 01:03:05 5463633
01/01/2007 01:04:05 36343643
01/01/2007 01:04:05 3636374
01/01/2007 01:04:05 6363636
01/01/2007 01:05:00 667746
01/01/2007 01:05:05 679846
01/01/2007 01:05:05 7847746
01/01/2007 01:06:05 89785746
01/01/2007 01:06:05 696546
01/01/2007 01:06:05 658658436
01/01/2007 01:07:05 35352
01/01/2007 01:07:05 77366
01/01/2007 01:07:05 67547754
01/01/2007 01:08:05 63636677
01/01/2007 01:08:05 657474
01/01/2007 01:08:05 43254358
01/01/2007 01:09:05 767856
01/01/2007 01:09:05 4747746
01/01/2007 01:10:00 9986964
01/01/2007 01:10:05 8574742
01/01/2007 01:11:05 4747466
01/01/2007 01:12:05 8747482
01/01/2007 01:13:05 87572
01/01/2007 01:14:05 54765480
01/01/2007 01:15:05 43264326

Results from query:

TimeBlock When AvgOfThrouhput
11255916 01/01/2007 01:01:00 2147006.16666667
11255917 01/01/2007 01:03:05 109735652.866667
11255918 01/01/2007 01:08:05 16124529.4444444
11255919 01/01/2007 01:13:05 32705792.6666667

Thanks,

Mark.
 
M

Michael Gramelspacher

Time_Stamp Throughput
01/01/2007 01:01:00 542520
01/01/2007 01:01:05 252520
01/01/2007 01:01:05 5432525
01/01/2007 01:02:05 52455
01/01/2007 01:02:05 6536361
01/01/2007 01:02:05 65656
01/01/2007 01:03:05 767463634
01/01/2007 01:03:05 767335
01/01/2007 01:03:05 5463633
01/01/2007 01:04:05 36343643
01/01/2007 01:04:05 3636374
01/01/2007 01:04:05 6363636
01/01/2007 01:05:00 667746
01/01/2007 01:05:05 679846
01/01/2007 01:05:05 7847746
01/01/2007 01:06:05 89785746
01/01/2007 01:06:05 696546
01/01/2007 01:06:05 658658436
01/01/2007 01:07:05 35352
01/01/2007 01:07:05 77366
01/01/2007 01:07:05 67547754
01/01/2007 01:08:05 63636677
01/01/2007 01:08:05 657474
01/01/2007 01:08:05 43254358
01/01/2007 01:09:05 767856
01/01/2007 01:09:05 4747746
01/01/2007 01:10:00 9986964
01/01/2007 01:10:05 8574742
01/01/2007 01:11:05 4747466
01/01/2007 01:12:05 8747482
01/01/2007 01:13:05 87572
01/01/2007 01:14:05 54765480
01/01/2007 01:15:05 43264326
Click to expand...

Using your data:

SELECT DATEADD("n",(DATEDIFF("n",0,[DataRates].Time_Stamp)\5)*5,0) AS [Time
Block],
Avg([DataRates].Throughput) AS [Avg Rate]
FROM DataRates
GROUP BY (DATEDIFF("n",0,[DataRates].Time_Stamp)\5)*5;



Time Block Avg Rate
1/1/2007 1:00:00 AM 69410024.3333333
1/1/2007 1:05:00 AM 67075760.6428571
1/1/2007 1:10:00 AM 14484951
1/1/2007 1:15:00 AM 43264326
 
M

Mark Rowe

Michael,

Thank you, it works great.

Thanks to everyone for their assistance.

Regards,

Mark.
 
M

Michel Walsh

Just in case one of the sample value does not make its way to the table
(whatever problem it can be), you can try:


SELECT a.timeStamp, AVG(b.secondField)
FROM myTable AS a INNER JOIN myTable As b
ON a.timeStamp >= b.timeStamp
AND b.timeStamp >= a.timeStamp - 5.0/(24.0*60.0)
GROUP BY a.timeStamp


So, for each row in the table myTable, this query returns the average of
secondField for all rows, in the same table, which refer to a time stamp
occurring at, or before the actual one, but not older than 5 minutes. The
constant 5.0/(24.0*60.0) is about 5 minutes expressed as a portion of a
full day.



Hoping it may help,
Vanderghast, Access MVP
 
T

Todd

Michael,

How would you do a similar query but for every 5 seconds given the data
was taken every second?

Michael Gramelspacher said:
Time_Stamp Throughput
01/01/2007 01:01:00 542520
01/01/2007 01:01:05 252520
01/01/2007 01:01:05 5432525
01/01/2007 01:02:05 52455
01/01/2007 01:02:05 6536361
01/01/2007 01:02:05 65656
01/01/2007 01:03:05 767463634
01/01/2007 01:03:05 767335
01/01/2007 01:03:05 5463633
01/01/2007 01:04:05 36343643
01/01/2007 01:04:05 3636374
01/01/2007 01:04:05 6363636
01/01/2007 01:05:00 667746
01/01/2007 01:05:05 679846
01/01/2007 01:05:05 7847746
01/01/2007 01:06:05 89785746
01/01/2007 01:06:05 696546
01/01/2007 01:06:05 658658436
01/01/2007 01:07:05 35352
01/01/2007 01:07:05 77366
01/01/2007 01:07:05 67547754
01/01/2007 01:08:05 63636677
01/01/2007 01:08:05 657474
01/01/2007 01:08:05 43254358
01/01/2007 01:09:05 767856
01/01/2007 01:09:05 4747746
01/01/2007 01:10:00 9986964
01/01/2007 01:10:05 8574742
01/01/2007 01:11:05 4747466
01/01/2007 01:12:05 8747482
01/01/2007 01:13:05 87572
01/01/2007 01:14:05 54765480
01/01/2007 01:15:05 43264326
Click to expand...

Using your data:

SELECT DATEADD("n",(DATEDIFF("n",0,[DataRates].Time_Stamp)\5)*5,0) AS [Time
Block],
Avg([DataRates].Throughput) AS [Avg Rate]
FROM DataRates
GROUP BY (DATEDIFF("n",0,[DataRates].Time_Stamp)\5)*5;



Time Block Avg Rate
1/1/2007 1:00:00 AM 69410024.3333333
1/1/2007 1:05:00 AM 67075760.6428571
1/1/2007 1:10:00 AM 14484951
1/1/2007 1:15:00 AM 43264326
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Interpolate Nearest Date 4
averaging lines of data at every ten lines 5
Averaging all values for each hour??? 11
Code to update links every 30 seconds 1
Adding Minutes and Seconds 5
COUNTIF Problem 1
Field for Minutes and Seconds 6
Data grouping and averaging in excel 2003 1

Top