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?

Simple: round each `DateTime` down to the nearest quarter of an hour, and group by that. That's definitely simple enough to do in LINQ to Objects - you might have difficulty if you're trying to do it in EF or LINQ to SQL or similar. — Jon Skeet, Aug 11 '16 at 08:28

score 4 · Accepted Answer · answered Aug 11 '16 at 09:16

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

score 2 · Answer 2 · edited May 23 '17 at 11:51

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

@DavidG, Yes, but actually I find your answer better. So I'll remove mine. — smoksnes, Aug 11 '16 at 10:19
No harm in leaving this here (with the edit, it also fixes an issue) — DavidG, Aug 11 '16 at 10:20
@smoksnes Could you please elaborate `it will not work with EF` (u mean EntityFramework r8?)? — SohamC, Aug 11 '16 at 10:31
@SohamC, it _might_ work with EF, but I doubt that EF will be able to convert the extension method to SQL. Also, unlike the answer from **DavidG** it re-iterates the collection, which could be bad for performance. — smoksnes, Aug 11 '16 at 10:35
@smoksnes FYI this definitely won't work with EF as EF cannot translate the `RoundDown` method. — DavidG, Aug 11 '16 at 11:08

Groupby DateTime for specific intervals

2 Answers2

Linked