I have lots of events with timestamps like "15/07/2017 12:38:20" with accompanying information for each event. I want to find the length of time it takes for most events to occur on a given day, and over multiple days using averages and standard deviations.

I could calculate the time between first and last timestamp of the day, but there are outliers which skew the data, so by using averages and standard deviations I can implement the 68-95-99% statistical rule to calculate the time it takes for 68% of events to occur (or 95% or 99%), and add boxplots etc. The data is distributed normally.

I have extracted the time part of each timestamp (e.g. 12:38:20) and converted that into seconds (a day has 86,400) by multiplying the hour by 3,600 seconds, minutes by 60 seconds and then adding all the seconds together. 12:38:20 therefore is 45,500 seconds into the day.

So this works fine for a single day. For example:

Day 1: say 100 events occur. Mean = 33,317 seconds into the day (so around 9.15am, i.e. the mean event occurred at this time) Stdev.p = 3,378 seconds (1 stdev away from the mean, so around 56 minutes 18 seconds) Stdev.p*2 = 6,757 seconds (between -1 and +1 stdev's away from the mean, this is the time it takes for 68% of events to occur, so around 1 hr 52 minutes)

All good so far. I can calculate this for every single day across my dataset.

My question is how to group multiple days together, so I can show the time it takes on average for a group of days (the whole week, month, quarter or whatever).

  1. Do I take the average of the standard deviations - i.e. stdev for day 1 + stdev for day 2, divided by the number of days?

  2. Or do I calculate the standard deviation for all events over days 1 and 2? Doing it this second way gives bigger durations across the two days.

As I'm typing this I'm feeling like its the first option. The second way mixes times from the first day with the second day, which is incorrect.

I think I've answered my own question, but would welcome any reassurance or comments to the contrary.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.