Average time from date time column?

[GWB]

I have a column that is in general date format, 6/19/2007 5:34:23 PM.
In another column I have a value between 10 and around 50. What I have to do
is get the average of this number by time in a day.

So if the number is 10 all day, then the average is 10. If the number is
changed during the day, say its 5 for half the day then 10 for the other half
of the day, then the number I'm looking for is 7.5.
If the number is 5 for 1/4 of the day and 10 for the other 3/4 of the day
then the number would be around 8.5..... if you follow what I'm trying to say.

So far I have been able to get the average number only using a query, but I
dont know how to put the time into the equation...

Thanks for any help

 

[Stefan Hoffmann]

hi,

I have a column that is in general date format, 6/19/2007 5:34:23 PM.
In another column I have a value between 10 and around 50. What I have to do
is get the average of this number by time in a day.

Your description is quite vague.

So if the number is 10 all day, then the average is 10. If the number is
changed during the day, say its 5 for half the day then 10 for the other half
of the day, then the number I'm looking for is 7.5.
If the number is 5 for 1/4 of the day and 10 for the other 3/4 of the day
then the number would be around 8.5..... if you follow what I'm trying to say.

How do you store this information in your table?

Can you provide some sample data? Can you also provide the desired
result for these samples.


mfG
--> stefan <--

 

[John W. Vinson]

I have a column that is in general date format, 6/19/2007 5:34:23 PM.
In another column I have a value between 10 and around 50. What I have to do
is get the average of this number by time in a day.

So if the number is 10 all day, then the average is 10. If the number is
changed during the day, say its 5 for half the day then 10 for the other half
of the day, then the number I'm looking for is 7.5.
If the number is 5 for 1/4 of the day and 10 for the other 3/4 of the day
then the number would be around 8.5..... if you follow what I'm trying to say.

So far I have been able to get the average number only using a query, but I
dont know how to put the time into the equation...

Thanks for any help

How dense are the time points? Are records added at regular intervals, or
(more likely I'd guess) randomly and irregularly - i.e. you might have only a
few records before 8am, a lot more from 8 to 11, slacking off over noon...? If
there is a three-hour period with no values at all, how should that affect the
average?

I would suggest that you consider an auxiliary table with evenly spaced time
points over the range of interest (midnight to midnight, 8 am to 5 pm,
whatever), with records spaced at some appropriate time interval (every
second, every minute, every five minutes...) You can then create a "non equi
join" query to put your observations into the time "bins" in this table and
average over that.

 

[KARL DEWEY]

John I would use a number table with DataAdd function to create the evenly
spaced time interval.