How can I average a DateTime field with a LINQ query?

Question

I have a database that has the following records in a DateTime field:

2012-04-13 08:31:00.000
2012-04-12 07:53:00.000
2012-04-11 07:59:00.000
2012-04-10 08:16:00.000
2012-04-09 15:11:00.000
2012-04-08 08:28:00.000
2012-04-06 08:26:00.000

I want to run a linq to sql query to get the average time from the records above. I tried the following:

(From o In MYDATA Select o.SleepTo).Average()

Since "SleepTo" is a datetime field I get an error on Average(). If I was trying to get the average of say an integer, the above linq query works.

What do I need to do to get it to work for datetimes?

Answer 1

Internally, every DateTime is really stored as a number of ticks. The Ticks property of a DateTime is defined as the "number of 100-nanosecond intervals that have elapsed since 12:00:00 midnight, January 1, 0001." (See http://msdn.microsoft.com/en-us/library/system.datetime.ticks.aspx)

You can convert the DateTimes to ticks, then average, then convert back to a datetime.

var averageTicks = (long) dates.Select(d => d.Ticks).Average();
var averageDate = new DateTime(averageTicks);

Using your data structures and formatting, it would look like this:

var averageTicks = (long)(from o in MYDATA select o.SleepTo.Ticks).Average();
var averageDate = new DateTime(averageTicks);

If you want to get the average time of each SleepTo value (ignoring the Date component), you can get the ticks of just the time:

var averageTicks = (long)(from o in MYDATA select o.SleepTo.TimeOfDay.Ticks).Average();
var averageTime = new TimeSpan(averageTicks);

Answer 2

Here are a couple extensions methods that can help with this... There is a core problem where if you have a lot of DateTimes in a list the LINQ average of a the ticks (long vars) will overflow.

    public static long Average(this IEnumerable<long> longs)
    {
        long count = longs.Count();
        long mean = 0;
        foreach (var val in longs)
        {
            mean += val / count;
        }
        return mean;
    }

    public static DateTime Average(this IEnumerable<DateTime> dates)
    {
        return new DateTime(dates.Select(x => x.Ticks).Average());
    }