Get average of the difference in datetime columns?

Question

I have an Item class

public class Item {
    //...
    public DateTime CreateDate { get; set; }
    public DateTime EndDate { get; set; }
}

In a method, I have List<Item> items. How would I query average of date differences in days more succinctly than looping through the list?

double days = 0;
int total = 0;

foreach (var @t in results)
{
    if (@t.EndDate != null)
    {
        total++;
        TimeSpan ts = (TimeSpan)(@t.EndDate - @t.CreateDate);
        days = ts.TotalDays;
    }
}

double avg = days / total;

@AndrewWhitaker - If `results` is of type `List`, then each instance of `@t` must be of type `Item`. — Thomas, May 28 '13 at 23:36
@Thomas: I understand that, but `CreateDate` and `EndDate` are not properties of `Item`. — Andrew Whitaker, May 29 '13 at 00:00
That's my mistake. They should be the same properties from `Item`. — Josh, May 29 '13 at 13:59

Thomas · Accepted Answer · 2013-05-29T21:17:12.397

9

var avg = results.Average( x => (x.EndDate - x.CreateDate).TotalDays );

Version that filters out null EndDate values and assuming that CreateDate is not of type DateTime?:

var avg = results.Where( x=> x.EndDate.HasValue)
    .Average( x => (x.EndDate.Value - x.CreateDate).TotalDays );

Edit

It was asked out to format the duration in the format of dd:HH:ss. To do that, we should average the total milliseconds (or seconds) and do something like:

var avg = results.Where(x=> x.EndDate.HasValue && x.CreateDate.HasValue)
    .Average( x => (x.EndDate.Value - x.CreateDate.Value).TotalMilliseconds );
var fancyOutput = TimeSpan.FromMilliseconds( avg );
Response.Write( fancyOutput.ToString() );

TimeSpan.ToString()

edited May 29 '13 at 21:17

answered May 28 '13 at 23:28

Thomas

63,911
12
95
141

Instead of `.TotalDays`, how would I format that as a string, `"dd\:hh\:ss"` instead? I tried `ToString` with that format, but it throws an error -- cannot implicitly convert `string` to `decimal?` (nullable). Note: I had to use `x.EndDate.Value - x.CreateDate.Value` since those values are also nullable. – Josh May 29 '13 at 19:42
@Josh - Done. In essence, you need to convert the total milliseconds or seconds to a `TimeSpan` instance and then output the format you want. – Thomas May 29 '13 at 21:17
That's the solution I came to as well! Thank you! – Josh May 29 '13 at 21:35

Get average of the difference in datetime columns?

1 Answers1

Edit