Groupby DateTime for specific intervals

Question

I have a list of objects of the following class:

public class CounterData 
{
    public DateTime counterTime { get; set; }
    public int counterName { get; set; }
    public int count { get; set; }
}

For example I have the following list of data in the format {counterTime, counterName, count}:

{"Aug  8 2016  9:00AM","counter1",11}
{"Aug  8 2016  9:05AM","counter2",12}
{"Aug  8 2016  9:11AM","counter3",47}
{"Aug  8 2016  9:12AM","counter3",20}
{"Aug  8 2016  9:13AM","counter1",12}
{"Aug  8 2016  9:30AM","counter3",61}
{"Aug  8 2016  9:35AM","counter2",35}
{"Aug  8 2016  9:39AM","counter1",16}
{"Aug  8 2016  9:40AM","counter1",92}
{"Aug  8 2016  9:53AM","counter2",19}

And I want to group the counters by counterName and counterTime aggregated for every 15 minute interval. For the above example the resultant list should be:

{"Aug  8 2016  9:00AM","counter1",23}
{"Aug  8 2016  9:00AM","counter2",12}
{"Aug  8 2016  9:00AM","counter3",67}
{"Aug  8 2016  9:30AM","counter3",61}
{"Aug  8 2016  9:30AM","counter2",35}
{"Aug  8 2016  9:30AM","counter1",108}
{"Aug  8 2016  9:45AM","counter2",19}

From 9:00AM-9:15AM, there were 2 entries of counter1. So the count value is a sum of the entries. Similarly for other counters.

Can we use LINQ GroupBy to solve this? If so then how?

Answer 1

Here's an example where you group date by 15 minutes. It might not be very efficient, and it will not work with EF. But as long as you're only doing LINQ to Objects you should be fine.

With the help of an extension from this question:

public static class DateTimeExtensions
{
    public static DateTime RoundDown(this DateTime dt, TimeSpan d)
    {
        return new DateTime(((dt.Ticks + 1) / d.Ticks) * d.Ticks);
    }
}

And this for grouping:

var counter = new List<CounterData>
{
    new CounterData {counterTime = DateTime.Now.AddMinutes(10), counterName = "counter1" },
    new CounterData {counterTime = DateTime.Now.AddMinutes(15), counterName = "counter2" },
    new CounterData {counterTime = DateTime.Now.AddMinutes(20), counterName = "counter3" },
    new CounterData {counterTime = DateTime.Now.AddMinutes(25), counterName = "counter3" },
    new CounterData {counterTime = DateTime.Now.AddMinutes(30), counterName = "counter1" },
    new CounterData {counterTime = DateTime.Now.AddMinutes(35), counterName = "counter3" },
    new CounterData {counterTime = DateTime.Now.AddMinutes(40), counterName = "counter2" },
    new CounterData {counterTime = DateTime.Now.AddMinutes(45), counterName = "counter1" },
    new CounterData {counterTime = DateTime.Now.AddMinutes(50), counterName = "counter1" },
    new CounterData {counterTime = DateTime.Now.AddMinutes(55), counterName = "counter2" },
};

// Now it's grouped by date.
var groupedCounters = counter.GroupBy(x => x.counterTime.RoundDown(TimeSpan.FromMinutes(15))).Select(d => new { Date = d.Key, Counters = d.ToList() });
List<CounterData> result = new List<CounterData>();

// With foreach.
foreach (var groupedCounter in groupedCounters)
{
    // Now we group by name as well.
    var countersByName =
        groupedCounter.Counters.GroupBy(x => x.counterName)
            .Select(
                x =>
                    new CounterData
                    {
                        count = x.Sum(item => item.Count),
                        counterTime = groupedCounter.Date,
                        counterName = x.Key
                    });
    result.AddRange(countersByName);
}

// Or with LINQ.

foreach (var countersByName in groupedCounters.Select(groupedCounter => groupedCounter.Counters.GroupBy(x => x.counterName)
    .Select(
        x =>
            new CounterData
            {
                count = x.Sum(item => item.Count),
                counterTime = groupedCounter.Date,
                counterName = x.Key
            })))
{
    result.AddRange(countersByName);
}

Answer 2

Here's a solution that rounds the times down to the nearest 15 minutes. It uses AddSeconds to essentially get rid of the seconds part of the DateTime, then AddMinutes with % to round down the minutes:

var data = counters
    .GroupBy(cd => new
    {
        Date = cd.counterTime.AddSeconds(-cd.counterTime.Second)
                   .AddMinutes(-cd.counterTime.Minute % 15),
        CounterName = cd.counterName
    })
    .Select(g => new
    {
        Date = g.Key.Date,
        CounterName = g.Key.CounterName,
        SumCount = g.Sum(cd => cd.count)
    });

However, this will not work with LINQ to Entities (i.e. Entity Framework), so you need to tweak it slightly to use the DbFunctions methods:

var data = counters
    .GroupBy(cd => new
    {
        Date = DbFunctions.AddMinutes(
            DbFunctions.AddSeconds(cd.counterTime, -cd.counterTime.Second), 
            -cd.counterTime.Minute % 15),
        CounterName = cd.counterName
    })
    .Select(g => new
    {
        Date = g.Key.Date,
        CounterName = g.Key.CounterName,
        SumCount = g.Sum(cd => cd.count)
    });