How to Linq query group by 2 hours interval

Question

I would like to query such that, the data is grouped by time (SQL time data type, not datetime), with 2 hours interval.

For example, the result would be something like this:

+---------------+-------+
|  Time Group   | Count |
+---------------+-------+
| 00:01 - 02:00 |     3 |
| 02:01 - 04:00 |     5 |
| 04:01 - 06:00 |     8 |
| ...           |   ... |
| 22:01 - 24:00 |     2 |
+---------------+-------+

Note that the time group is predefined. I managed to have a naive query like this:

var temp = from a in myTable select a;
var result0To2 = temp.Where(a => a.activityTime.Hours > 0 && a.activityTime.Hours <= 2).Count();
var result2To4 = temp.Where(a => a.activityTime.Hours > 2 && a.activityTime.Hours <= 4).Count();
var result4To6 = temp.Where(a => a.activityTime.Hours > 4 && a.activityTime.Hours <= 6).Count();
...
...
var result20To24 = temp.Where(a => a.activityTime.Hours > 22 && a.activityTime.Hours <= 24).Count();

Which I think should work. But is there a better way?

score 1 · Answer 1 · answered Dec 12 '17 at 04:04

Here is a test program I ran, you can adapt the code based on your specific requirements. It uses LINQ's GroupBy() predicate, and is based completely on this answer to a similar question (I changed it to check hours instead of minutes).

public class Temp
{
    public DateTime activityTime { get; set; }
    public object item_info { get; set; }
}

Of course you can use your own class, mine is just for testing.

List<Temp> items = new List<Temp>     //populate with dummy data
{
    new Temp { activityTime = DateTime.Parse("2017-12-11 11:43:00"), item_info = "something1" },
    new Temp { activityTime = DateTime.Parse("2017-12-11 11:46:00"), item_info = "something2" },
    new Temp { activityTime = DateTime.Parse("2017-12-11 11:57:00"), item_info = "something3" },
    new Temp { activityTime = DateTime.Parse("2017-12-11 12:02:00"), item_info = "something4" },
    new Temp { activityTime = DateTime.Parse("2017-12-11 12:04:00"), item_info = "something5" },
    new Temp { activityTime = DateTime.Parse("2017-12-11 12:06:00"), item_info = "something6" },
    new Temp { activityTime = DateTime.Parse("2017-12-11 12:58:00"), item_info = "something7" },
    new Temp { activityTime = DateTime.Parse("2017-12-11 13:05:00"), item_info = "something8" },
    new Temp { activityTime = DateTime.Parse("2017-12-11 13:29:00"), item_info = "something9" },
    new Temp { activityTime = DateTime.Parse("2017-12-11 14:53:00"), item_info = "something10" },
    new Temp { activityTime = DateTime.Parse("2017-12-11 14:55:00"), item_info = "something11" },
    new Temp { activityTime = DateTime.Parse("2017-12-11 14:59:00"), item_info = "something12" },
    new Temp { activityTime = DateTime.Parse("2017-12-11 14:59:00"), item_info = 13 },
    new Temp { activityTime = DateTime.Parse("2017-12-11 15:26:00"), item_info = 15 }
};

var groups = items.GroupBy(x =>
{
    var stamp = x.activityTime;
    stamp = stamp.AddMinutes(-(stamp.Minute));
    stamp = stamp.AddMilliseconds(-stamp.Millisecond - 1000 * stamp.Second);
    return stamp;
})
.Select(g => new { TimeGroup = g.Key.ToString("h tt") + " - " + g.Key.AddHours(1).ToString("h tt"), Value = g.Count() })
.ToList();

To change the grouping, you can modify the GroupBy() predicate.

The TimeGroup variable can be changed based on what display you need (date formatting, or you can add 1 minute like in your case to have "02:01 - 04:00".

For an interval of 2 hours:

var groups = items.GroupBy(x =>
{
    var stamp = x.activityTime;
    stamp = stamp.AddHours(-(stamp.Hour % 2));
    stamp = stamp.AddMinutes(-(stamp.Minute));
    stamp = stamp.AddMilliseconds(-stamp.Millisecond - 1000 * stamp.Second);
    return stamp;
})
.Select(g => new { TimeGroup = g.Key.ToString("h tt") + " - " + g.Key.AddHours(2).ToString("h tt"), Value = g.Count() })
.ToList();

Warning, this operation may be time-heavy for larger datasets.

How to Linq query group by 2 hours interval

1 Answers1