CODEWITHSUNDEEP
c#linqgroup-by
Eric Bergman
Eric Bergman

Reputation: 1443

LINQ: Group by Date Range within 5 minutes

Let's say I have the following collection of items and Dates:

ItemID: 1, Date: 10/10/2018 11:30 AM
ItemID: 1, Date: 10/10/2018 11:32 AM
ItemID: 1, Date: 10/10/2018 11:33 AM
ItemID: 1, Date: 10/10/2018 11:34 AM
ItemID: 1, Date: 10/10/2018 11:57 AM

ItemID: 2, Date: 10/10/2018 7:45 AM
ItemID: 2, Date: 10/10/2018 7:49 AM

ItemID: 3, Date: 10/10/2018 8:45 AM
ItemID: 3, Date: 10/10/2018 9:13 AM

I'd like to group by ItemID and Date but the dates I want them group by the ones that fall within 5 minutes of each other

In the data set above the following would be grouped

ItemID: 1, Date: 10/10/2018 11:30 AM    <-- Grouped with Item 1
ItemID: 1, Date: 10/10/2018 11:32 AM    <-- Grouped with Item 1
ItemID: 1, Date: 10/10/2018 11:33 AM    <-- Grouped with Item 1
ItemID: 1, Date: 10/10/2018 11:34 AM    <-- Grouped with Item 1


ItemID: 1, Date: 10/10/2018 11:57 AM    <-- Not Grouped


ItemID: 2, Date: 10/10/2018 7:45 AM    <-- Grouped with Item 2
ItemID: 2, Date: 10/10/2018 7:49 AM    <-- Grouped with Item 2


ItemID: 3, Date: 10/10/2018 8:45 AM    <-- Not Grouped

ItemID: 3, Date: 10/10/2018 9:13 AM    <-- Not Grouped

The ones that are grouped are the ones that fall within 5 minutes of each other.

I know how to group them by ItemID and Date in LINQ but I'm having a hard time grouping them the way I described.

What I've tried so far:

var groupedItems = from item in items
              group item by new
              {
                  item.ItemID,
                  item.Date
              } into g
              select g;

Upvotes: 4

Views: 3299

Answers (2)

arekzyla
arekzyla

Reputation: 2898

You can try something like this:

var groupedItems = items
    .GroupBy(i => i.ItemId, (k, g) => g
        .GroupBy(i => (long)new TimeSpan(i.Date.Ticks - g.Min(e => e.Date).Ticks).TotalMinutes / 5))
    .SelectMany(g => g);

Upvotes: 5

CodeHxr
CodeHxr

Reputation: 885

My attempt at your solution:

var dates = new List<DateTime>{ ... }; // dates get initialized however your code does it...

var groups = dates.Select(d => 
    d, 
    GroupDate = new DateTime(d.Year, d.Month, d.Day, d.Hour, (d.Minute / 5) * 5, d.Second)})
    .GroupBy(g => g.GroupDate);

The math for the Minute is going to, ultimately, separate by which whole multiple of 5 the minute falls into. Another caveat is that if you don't care about day/month/year and want to group everything explicitly by minute, then scratch the new DateTime(...) piece and replace with (d.Minute / 5) * 5. That will group by minute regardless of day/month/year.

Hope this helps!

Upvotes: 2

Related Questions