CODEWITHSUNDEEP
c#linqlinq-to-sql
Jade
Jade

Reputation: 53

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

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?

Upvotes: 5

Views: 5950

Answers (3)

jocull
jocull

Reputation: 21175

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());
    }

Upvotes: 4

Duane Theriot
Duane Theriot

Reputation: 2185

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);

Upvotes: 10

yamen
yamen

Reputation: 15618

The database LINQ provider doesn't seem to understand how to do averages on absolute dates. Which, if you think about it, makes sense (average is a sum divided by a count - what is the sum?).

So, if you're not able to run the following:

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

Then you won't be able to do .Average() also.

Since what you want is actually the average time of SleepTo, you need to get just the time component of the date as a TimeSpan (time minus midnight perhaps) and average that. Do you by any chance have SleepFrom?

In the meantime, you might find this post enlightening: LINQ Average TimeSpan?

Upvotes: 1

Related Questions