Average results by day

[Binraider]

Hi all,

Hopefully an easy one for a Tuesday afternoon.

I have a large data set in a single table, using Site Name and Date/Time as the primary key. Data associated with it is listed as C1, C2, C3..., etc. Date/Time is recorded at roughly 4 minute intervals. There are approx 136,000 records per site, per year, times around 20 unique sites.

What I'd like to do is have a query to take the average of values in each 24 hour block starting at from 06:00, through to 06:00. I could make do with Calendar day, but 06:00 would be preferred.

It's a trivial problem I think, but mental block is stopping me figuring it out - and the last thing I want to do is to grind the data in Excel instead which seems totally unnecessary!

Don't think it matters too much, but for interest I'm Access 2003 as a client to Oracle 9 via ODBC to run my query.

Thanks in advance,

Dave

 

[jasmith4]

By "06:00" you mean 6:00 AM, right? So since days go from midnight to midnight, you need a six-hour adjustment.

First make a new query based on your table which has all the table's fields and is sorted in the same order as the primary key. Assuming your date/time field is called "DateTime", add a calculated field called "AdjustedDateTime", whose expression is [DateTime] - TimeSerial(6,0,0). (The simpler way is [DateTime] - 0.25, since six hours is a quarter day, but it's not as flexible or comprehensible).

You can then make a query based on the above query which groups by AdjustedDateTime rather than DateTime to do averages, sums, etc. -- don't forget to click the Greek-sigma button!

 

[xenou]

Maybe you can also coerce your time into buckets using a "case" type structure.

Assume Field1 is the field with the time in it.

Switch([Field1]<0.25,TimeSerial(0,0,0),[Field1]<0.5,TimeSerial(6,0,0),[Field1]<0.75,TimeSerial(12,0,0),[Field1]<1,TimeSerial(18,0,0))

Or simpler math:

TimeSerial(Int([Field1]*4)*6,0,0)

This should return the "start hour" of each of the 4 intervals in the day:
00:00 AM, 6:00 AM, 12:00 PM, and 6:00 PM

I'm assuming that you would actually end up with most or all your times in bucket 2 and 3 (6:00 AM -12:00 PM or 12:00 PM -6:00 PM), since you aren't collecting data before 6:00 AM or after 6:00 PM.

You may want to adjust the boundary so that they end on the times rather than start on the times. It becomes a question of where do you want exact times falling on the hour to fall.

 

[jasmith4]

xenou: I don't think your Switch will work, because the field has both date and time information, so it will always be greater than 1. But your simpler-math way definitely will work to provide a field for grouping over six-hour increments.

But I think BinRaider wants averages over blocks of 24 hours, not six hours, each of which starts at 6AM every day -- sort of like a "fiscal day" -- and that's what my AdjustedDateTime field accomplishes.

 

[xenou]

Same formula for whole dates:
Int([Field1])+TimeSerial((Int(([Field1]-Int([Field1]))*4))*6,0,0)

JSmith: No problem calling the first six hours of the day this or that ... I think you can just subtract the .25 as you noted above, or use the 6-12 bucket as the first of each day.