Grouping by Time ranges in Linq

Question

I've been getting stuck into some linq queries for the first time today and I'm struggling with some of the more complicated ones. I'm building a query to extract data from a table to build a graph. The tables colums I'm interested in are Id, Time and Value.

The user will select a start time, an end time and the number of intervals (points) to graph. The value column will averaged for each interval.

I can do this with a linq request for each interval but I'm trying to write it in one query so I only need to go to the database once.

So far I have got:

var timeSpan = endTime.Subtract(startTime);
var intervalInSeconds = timeSpan.TotalSeconds / intervals;

var wattList = (from t in _table
                where t.Id == id
                    && t.Time >= startTime
                    && t.Time <= endTime
                group t by  intervalInSeconds // This is the bit I'm struggling with
                    into g
                    orderby g.Key 
                    select g.Average(a => a.Value))
                ).ToList();

Any help on grouping over time ranges will be most welcome.

Answer 1

Check out this example I wrote a while ago. It sounds like what you are trying to do, but I'm not sure if it does the grouping in SQL or by .NET.

http://mikeinmadison.wordpress.com/2008/03/12/datetimeround/

Answer 2

Maybe you can do something like:

var wattList = (from t in _table
                where t.Id == id
                   && t.Time >= startTime
                   && t.Time <= endTime
               ).GroupBy(x => (int) ((x.Time - startTime).TotalSeconds / intervalInSeconds))
                .Select(grp => grp.Average(x => x.Value));

Answer 3

I've done this myself for exactly the same situation you describe.

For speed, modified the database's datapoints table to include an integer-based time column, SecondsSince2000, and then worked with that value in my LINQ to SQL query. SecondsSince2000 is a computed column defined as:

datediff(second, dateadd(month,1200,0), DataPointTimeColumn) PERSISTED

Where DataPointTimeColumn is the name of the column that stores the datapoint's time. The magic function call dateadd(month,1200,0) returns 2000-01-01 at midnight, so the column stores the number of seconds since that time.

The LINQ to SQL query is then made much simpler, and faster:

int timeSlotInSeconds = 60;

var wattList = 
    (from t in _table
     where t.Id == id
           && t.Time >= startTime
           && t.Time <= endTime
     group t by t.SecondsSince2000 - (t.SecondsSince2000 % timeSlotInSeconds)
     into g
     orderby g.Key 
     select g.Average(a => a.Value))).ToList();

If you can't modify your database, you can still do this:

var baseTime = new DateTime(2000, 1, 1);

var wattList = 
    (from t in _table
     where t.Id == id
           && t.Time >= startTime
           && t.Time <= endTime
     let secondsSince2000 = (int)(t.Time- baseTime).TotalSeconds
     group t by secondsSince2000 - (secondsSince2000 % timeSlotInSeconds)
     into g
     orderby g.Key 
     select g.Average(a => a.Value))).ToList();

The query will be quite a bit slower.