Group And Average Timespans By Week

Question

I have a number of records with various DateTimes, but I need to track these in a graphical layout. I currently have managed to retrieve the timespan of each record

List<myclass> recs = context.myclass.Where(c => c.RequestedTimestamp >= start & c.DeliveredTimestamp <= end).ToList();

foreach (var item in recs) 
{
    TimeSpan diff = (TimeSpan)(item.DeliveredTimestamp - item.RequestedTimestamp);

    //more to come, this is where I realized I have issues
}

This of course, will return me a list of timespans, but that in itself is not very helpful.

How would this be better approached in order to achieve a list of weekly (or monthly) average of these timespans.

The problem I consider is that timespans forget their start and end points, therefore whilst these can be averaged, how is the averaging going to be performed in a grouped approach?

The output; an average timespan for a group of DateTimes, is to be hooked into a chart for a visual display.

Currently your problem is not clear. What's wrong with the `TimeSpan` that you calculate in the `foreach`? You are not creating a list so i don't understand "will return me a list of timespans". What are you doing in the foreach, why doesn't it work as expected? Or what are you actually trying to achieve? — Tim Schmelter, Apr 20 '15 at 14:58
It's not clear here. A `TimeSpan` won't let you group by weekly or monthly averages, because a `TimeSpan` is just an arbitrary amount of time. I think what you're really asking is to group your `recs` by each week somehow, then get the timespans for each item in each grouping. — Jonesopolis, Apr 20 '15 at 15:02
I neglected to put in my list for the foreach because i got halfway through the line and got stumped, realising that what I had in mind was not what i was hoping for. As for the timespan, i mentioned that i realised that it is simply a number with no point of reference. But yes jonesy, I want to group by week/month then find an average timespan for that group — nickson104, Apr 20 '15 at 15:05
There could also be time zone issues here. I assume deliveries could be made in many different time zones. Are your timestamps UTC or local time? Whose perspective are you grouping by - the person who received the delivery? Or the person viewing the report? You might also consider using [`DateTimeOffset`](http://stackoverflow.com/a/14268167/634824) instead of `DateTime`. — Matt Johnson-Pint, Apr 20 '15 at 15:19
Time zones are not an issue, the timestamps are all within a local time zone. Im thinking that the groups will be from the delivered timestamp, however in reality these timestamps should all fall within a day really unless there are issues. The datetimeoffset looks useful, but this application will only really be accessible in one area — nickson104, Apr 21 '15 at 07:17
If that local time zone does [daylight saving time](http://stackoverflow.com/tags/dst/info), and these timestamps could cross one of the transitions, then you will still have the possibility of bad data - even in a single time zone. — Matt Johnson-Pint, May 04 '15 at 21:38
Also, Is the `context` an Entity Framework context? or something else? The grouping and averaging you want is usually best done in the original query, rather than after materialization (before the `ToList`, not after). — Matt Johnson-Pint, May 04 '15 at 21:50
Both are good points, I hadn't thought of those, I had neglected to update this question with the solution I took, I shall update that now — nickson104, May 05 '15 at 07:44

score 0 · Accepted Answer · answered May 05 '15 at 07:50

This is probably not the best solution that was available, and there are some definite holes in the logic, however for now this is a solution that works to the extent that is needed (low priority system therefore high accuracy is not needed). Also, sorry about all the censored variables in there such as the class name etc, i understand its more of a pain to read.

public static List<ChartPoint> RenderWeeklyAverageChart(List<myclass> myclass, DateTime start, DateTime end)
        {
            DateTime datePointer = start; //1st date in list
            List<ChartPoint> points = new List<ChartPoint>();
            double average = 0;

            if (myclass!= null && myclass.Count > 0)
            {
                while (datePointer < DateTime.Now)
                {
                    //Filter By Selected Week
                    List<myclass> Weekmyclass = myclass.Where(c => c.RequestedTimestamp >= datePointer &&
                        c.RequestedTimestamp < datePointer.AddDays(7)).ToList();
                    //If there are records, find the average timespan of the records
                    if (Weekmyclass != null && Weekmyclass.Count > 0)
                    {
                        long ticks = (long)WeekSlabs.Average(c => (c.DeliveredTimestamp.Value - c.RequestedTimestamp.Value).Ticks);
                        average = TimeSpan.FromTicks(ticks).TotalHours;

                        points.Add(new ChartPoint
                        {
                            PointValue = average,
                            AxisXText = datePointer.ToShortDateString() + " - "
                                        + datePointer.AddDays(7).ToShortDateString()
                        });
                    }
                    else
                    {
                        //Otherwise Add a Zero Record
                        points.Add(new ChartPoint
                        {
                            PointValue = 0,
                            AxisXText = datePointer.ToShortDateString() + " - "
                                        + datePointer.AddDays(7).ToShortDateString()
                        });
                    }
                    datePointer = datePointer.AddDays(7);
                }
            }
            return points;
        }

Group And Average Timespans By Week

1 Answers1