Question Average time of day calculation

[tezread]

I have been doing some trial and error with this and not sure if it is right. This is simple in principle!I have a table with the fieldsEpisodeIDEpisodeDateTimeIs it possible to calculate from this:a) the average time of day episodes occur (mean/median)b) the minimum maximum number of episodes in 1 dayBest wishesTez

 

[jdraw]

Have you tested your idea? What results/errors did you receive?
Do you have test data, real (observation) data?
Set it up, run some tests.
You know you situation better than any of us.

 

[tezread]

So far my test has involvedsetting up a cross tab query that counts the number of episodes per day (this was quite long as there are 274 episodes occuring on 191 days)

 

[jdraw]

My thinking was could you set up some tests (say 5 days , 3 episodes per day) that was manageable to confirm your process and calculations. Once you get it working for a few days and a few episodes, it should work for a larger number. It's just that testing with 270+ episodes over 191 days seems extreme for testing.

You could separately calculate the averages or whatever you need in advance( to predict what the true number is), and then run your "processor" against the test data and see (the result). Was the result = the expected? If no, can you account for the difference? Perhaps you need to adjust your "processor/algorithm", then run it again.

 

[gemma-the-husky]

if you understand it, yes you can.

a date type includes both the date and time. you probably need to get at just the time bit - but then yuo can certainly average total times

using times does give you a bit of of a problem, as all time is modulo 24hrs - but if you are only interested in the time of day, that cannot give you an issue.

you need to watch out for instances where someone has just entered a date, and no time (ie 12.00 midnight) as that will skew your results.