Average time from a list of start end times in linq to sql
Asked Answered
I

3

2

I have a list of

*startTime    (datetime)
*endTime      (datetime)

and need to work out the average time for everything in the list.

So I am guessing I need something like

long averageTime = 
 Convert.ToInt64(listOfStartEndTimes.Average(timeSpan => timeSpan.Ticks))

Any ideas?

Incurious answered 25/7, 2013 at 8:59 Comment(0)
A
4
long averageTime = listOfStartEndTimes
                       .Select(se => se.End - se.Start)
                       .Average(t => t.Ticks);

Or alternatively, with a slightly lower resolution (unix epoch date instead):

long averageTime = listOfStartEndTimes
                       .Select(se => se.End - se.Start)
                       .Average(t => (t.Ticks – 621355968000000000) / 10000000);
Abeokuta answered 25/7, 2013 at 9:1 Comment(8)
Perfect! Then simple to convert to hours minutes secondsIncurious
@Incurious Actually I deleted that comment because I thought I misunderstood what average you're calculating. Are you calculating the average for each pair of start/end times (so you only add two TickCounts together, which won't overflow) or are you wanting the average of all times (in which case you will be likely to get an overflow problem)?Commentate
It will be the average of the whole list of start/end times.Incurious
@Incurious Ah ok, so it will be a problem then. Since there are 10,000 ticks per millisecond, if you divide all the tick counts by 10,000 when calculating the average, then multiply the average result by 10,000 before converting it back to a DateTime it should still have (approx) millisecond accuracy, and it wouldn't overflow so quickly (10,000 times less quickly in fact ;)Commentate
Yeahh thanks. Infact the average time only needs to be in hours minutes seconds so would that make a difference?Incurious
@Incurious If the times always fall within the same day you could just convert it all to seconds since midnight, but if the times can straddle a midnight it gets much more fiddly, and then it would be easier to just use DateTime.Ticks. Note: var secsSinceMidnight = (DateTime.Now - DateTime.Today).TotalSeconds;Commentate
Actually as I only need hours/minutes/seconds I could just extract these from the datetimes before adding them to the list?Incurious
@Incurious I think you could do that ok.Commentate
R
2

The other answers are correct that you can average TimeSpan properties with simple LINQ. I prefer letting the TimeSpan class do it's work to determine precision, such as Uffe showed. But make sure you are using the correct property. .Seconds will return the remainder of seconds that don't fit into a full minute. You want the .TotalSeconds property.

Using .Ticks might be tempting, but you could easily get an OverflowException if you have a lot of values and they are spaced far enough apart from each other. My recommendation is to use one unit smaller than you need in your result. So if you care about average precision to the minute, then .TotalSeconds should work well. You can then take the result back into a TimeSpan if you like.

var sec = yourList.Select(p => p.Stop - p.Start).Average(p => p.TotalSeconds);
var avg = TimeSpan.FromSeconds(sec);

Also, since your Start and Stop values are of type DateTime, then you really need to pay attention to their .Kind. An operation like this is only guaranteed to be accurate if yourDateTime.Kind == DateTimeKind.Utc.

If they are Local kinds, then the result will be influenced by the time zone of the local computer that is running the code. If they are Unspecified, then the data may have been recorded in the context of the local time zone, or of some other time zone. If the times you are working in cover a daylight saving time transition, then your result of subtraction may be incorrect.

For example, if you are running this code in the US Pacific Time zone, and you have Local kinds of DateTime, then subtracting 2013-11-03 06:00 - 2013-11-03 00:00 would give you a TimeSpan of 6 hours. But in reality, 7 hours will have elapsed, since that is the day DST ends and the clocks repeat the hour between 1:00 and 2:00.

To avoid this problem, you should only do math with DateTime values that are in Utc, or you should use DateTimeOffset values instead.

Rexanna answered 30/7, 2013 at 15:11 Comment(2)
Yes a good reply thank you. Essentially I only need the average in seconds and this will be an internal application so the .kind will be constantIncurious
Just because its an internal app doesn't relieve the need to consider the kind. My advice is to use DateTimeOffset.Rexanna
A
0

If you have a class

public class Time
{
    public DateTime Start;
    public DateTime Stop;

    public Time(DateTime start, DateTime stop)
    {
        this.Start = start;
        this.Stop = stop;
    }
}

That you fill with datetime values you can probably get the average with...

var avg = Times.Select(p => p.Stop - p.Start).Average(p => p.Seconds);

Where you can replace p.Seconds with whatever you would like.. Hours, minutes etc... Note: Not tested, but I think it might work

Edit: Ahh, already answered :)

Adrial answered 25/7, 2013 at 9:11 Comment(2)
Thanks for your contributionIncurious
I think you mean .TotalSeconds, not .Seconds.Rexanna

© 2022 - 2024 — McMap. All rights reserved.