Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Grouping by every n minutes

Tags:

c#

linq

I'm playing around with LINQ and I was wondering how easy could it be to group by minutes, but instead of having each minute, I would like to group by every 5 minutes.

For example, currently I have:

var q = (from cr in JK_ChallengeResponses
        where cr.Challenge_id == 114
        group cr.Challenge_id 
            by new { cr.Updated_date.Date, cr.Updated_date.Hour, cr.Updated_date.Minute } 
            into g 
        select new {
          Day = new DateTime(g.Key.Date.Year, g.Key.Date.Month, g.Key.Date.Day, g.Key.Hour, g.Key.Minute, 0),
          Total = g.Count()
        }).OrderBy(x => x.Day);

enter image description here

What do I have to do to group my result for each 5 minutes?

like image 303
balexandre Avatar asked Aug 21 '12 18:08

balexandre


1 Answers

To group by n of something, you can use the following formula to create "buckets":

((int)(total / bucket_size)) * bucket_size

This will take the total, divide it, cast to integer to drop off any decimals, and then multiply again, which ends up with multiples of bucket_size. So for instance (/ is integer division, so casting isn't necessary):

group cr.Challenge_id
    by new { cr.Updated_Date.Year, cr.Updated_Date.Month, 
             cr.Updated_Date.Day, cr.Updated_Date.Hour,
             Minute = (cr.Updated_Date.Minute / 5) * 5 }
like image 121
mellamokb Avatar answered Nov 07 '22 06:11

mellamokb