Group datetime by an interval of minutes-CodePudding

I'm trying to group my list using linq by an interval of 30 minutes.

Let’s say we have this list:

X called at 10:00 AM
Y called at 10:10 AM
Y called at 10:20 AM
Y called at 10:35 AM
X called at 10:40 AM
Y called at 10:45 AM

What i need is to group these items in a 30 minutes frame and by user, like so:

X called at 10:00 AM
Y called 3 times between 10:10 AM and 10:35 AM
X called at 10:40 AM
Y called at 10:45 AM

Here's what i'm using with Linq:

myList
.GroupBy(i => i.caller, (k, g) => g
.GroupBy(i => (long)new TimeSpan(Convert.ToDateTime(i.date).Ticks - g.Min(e => Convert.ToDateTime(e.date)).Ticks).TotalMinutes / 30)
.Select(g => new
{
  count = g.Count(),
  obj = g
}));

I need the result in one list, but instead im getting the result in nested lists, which needs multiple foreach to extract.

Any help is much appreciated!

CodePudding user response：

Instead of grouping by a DateTime, try grouping by a key derived from the date.

string GetTimeBucketId(DateTime time) {
    return $"${time.Year}-{time.Month}-{time.Day}T{time.Hour}-{time.Minute % 30}";
}

myList
  .GroupBy(i => GetTimeBucketId(i.caller.date))
  .Select(g => { Count = g.Count(), Key = g.Key });

CodePudding user response：

I think you are looking for SelectMany which will unwind one level of grouping:

var ans = myList
            .GroupBy(c => c.caller, (caller, cg) => new { Key = caller, MinDateTime = cg.Min(c => c.date), Calls = cg })
            .SelectMany(cg => cg.Calls.GroupBy(c => (int)(c.date - cg.MinDateTime).TotalMinutes / 30))
            .OrderBy(cg => cg.Min(c => c.date))
            .ToList();

Note: The GroupBy return selects the Min as a minor efficiency improvement so you don't constantly re-find the minimum DateTime for each group per call.

Note 2: The (int) conversion creates the buckets - otherwise, .TotalMinutes returns a double and the division by 30 just gives you a (unique) fractional answer and you get no grouping into buckets.

By modifying the initial code (again for minor efficiency), you can reformat the answer to match your textual result:

var ans = myList
            .GroupBy(c => c.caller, (caller, cg) => new { Key = caller, MinDateTime = cg.Min(c => c.date), Calls = cg })
            .SelectMany(cg => cg.Calls.GroupBy(c => (int)(c.date - cg.MinDateTime).TotalMinutes / 30), (bucket, cg) => new { FirstCall = cg.MinBy(c => c.date), Calls = cg })
            .OrderBy(fcc => fcc.FirstCall.date)
            .ToList();

var ans2 = ans.Select(fcc => new { Caller = fcc.FirstCall.caller, FirstCallDateTime = fcc.FirstCall.date, LastCallDateTime = fcc.Calls.Max(c => c.date), Count = fcc.Calls.Count() })
              .ToList();